<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Chapter 7 Backup and Recovery</title>
<link rel="stylesheet" href="mvl.css" type="text/css" />
<meta name="generator" content="DocBook XSL Stylesheets + chunker.py v1.9.2" />
<link rel="start" href="index.html" title="{book-title}" />
<link rel="up" href="" title="" />
<link rel="prev" href="security.html" title="Chapter 6 Security" />
<link rel="next" href="optimization.html" title="Chapter 8 Optimization" />
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader">
<table width="100%" summary="Navigation header">
<tr>
<th colspan="3" align="center">Chapter 7 Backup and Recovery</th>
</tr>
<tr>
<td width="20%" align="left"><a accesskey="p" href="security.html">Prev</a> </td>
<th width="60%" align="center"></th>
<td width="20%" align="right"> <a accesskey="n" href="optimization.html">Next</a></td>
</tr>
</table>
<hr>
</div>
<div class="chapter">
<div class="titlepage">
<div>
<div>
<h1 class="title"><a name="backup-and-recovery"></a>Chapter 7 Backup and Recovery</h1>

</div>

</div>

</div>
<div class="toc">
<p><b>Table of Contents</b></p><dl class="toc"><dt><span class="section"><a href="backup-and-recovery.html#backup-types">7.1 Backup and Recovery Types</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#backup-methods">7.2 Database Backup Methods</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#backup-strategy-example">7.3 Example Backup and Recovery Strategy</a></span></dt><dd><dl><dt><span class="section"><a href="backup-and-recovery.html#backup-policy">7.3.1 Establishing a Backup Policy</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#recovery-from-backups">7.3.2 Using Backups for Recovery</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#backup-strategy-summary">7.3.3 Backup Strategy Summary</a></span></dt></dl></dd><dt><span class="section"><a href="backup-and-recovery.html#using-mysqldump">7.4 Using mysqldump for Backups</a></span></dt><dd><dl><dt><span class="section"><a href="backup-and-recovery.html#mysqldump-sql-format">7.4.1 Dumping Data in SQL Format with mysqldump</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#reloading-sql-format-dumps">7.4.2 Reloading SQL-Format Backups</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#mysqldump-delimited-text">7.4.3 Dumping Data in Delimited-Text Format with mysqldump</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#reloading-delimited-text-dumps">7.4.4 Reloading Delimited-Text Format Backups</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#mysqldump-tips">7.4.5 mysqldump Tips</a></span></dt></dl></dd><dt><span class="section"><a href="backup-and-recovery.html#point-in-time-recovery">7.5 Point-in-Time (Incremental) Recovery Using the Binary Log</a></span></dt><dd><dl><dt><span class="section"><a href="backup-and-recovery.html#point-in-time-recovery-times">7.5.1 Point-in-Time Recovery Using Event Times</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#point-in-time-recovery-positions">7.5.2 Point-in-Time Recovery Using Event Positions</a></span></dt></dl></dd><dt><span class="section"><a href="backup-and-recovery.html#myisam-table-maintenance">7.6 MyISAM Table Maintenance and Crash Recovery</a></span></dt><dd><dl><dt><span class="section"><a href="backup-and-recovery.html#myisam-crash-recovery">7.6.1 Using myisamchk for Crash Recovery</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#myisam-check">7.6.2 How to Check MyISAM Tables for Errors</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#myisam-repair">7.6.3 How to Repair MyISAM Tables</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#myisam-optimization">7.6.4 MyISAM Table Optimization</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#myisam-maintenance-schedule">7.6.5 Setting Up a MyISAM Table Maintenance Schedule</a></span></dt></dl></dd></dl>
</div>
<a class="indexterm" name="idm140710722820256"></a><a class="indexterm" name="idm140710722818768"></a><p>
    It is important to back up your databases so that you can recover
    your data and be up and running again in case problems occur, such
    as system crashes, hardware failures, or users deleting data by
    mistake. Backups are also essential as a safeguard before upgrading
    a MySQL installation, and they can be used to transfer a MySQL
    installation to another system or to set up replication slave
    servers.
  </p><p>
    MySQL offers a variety of backup strategies from which you can
    choose the methods that best suit the requirements for your
    installation. This chapter discusses several backup and recovery
    topics with which you should be familiar:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        Types of backups: Logical versus physical, full versus
        incremental, and so forth.
      </p></li><li class="listitem"><p>
        Methods for creating backups.
      </p></li><li class="listitem"><p>
        Recovery methods, including point-in-time recovery.
      </p></li><li class="listitem"><p>
        Backup scheduling, compression, and encryption.
      </p></li><li class="listitem"><p>
        Table maintenance, to enable recovery of corrupt tables.
</p></li></ul>
</div>
<h2><a name="idm140710722811536"></a>Additional Resources</h2>
<p>
    Resources related to backup or to maintaining data availability
    include the following:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        Customers of MySQL Enterprise Edition can use the MySQL Enterprise Backup product
        for backups. For an overview of the MySQL Enterprise Backup
        product, see <a class="xref" href="mysql-enterprise.html#mysql-enterprise-backup" title="29.2 MySQL Enterprise Backup Overview">Section 29.2, “MySQL Enterprise Backup Overview”</a>.
      </p></li><li class="listitem"><p>
        A forum dedicated to backup issues is available at
        <a class="ulink" href="http://forums.mysql.com/list.php?28" target="_top">http://forums.mysql.com/list.php?28</a>.
      </p></li><li class="listitem"><p>
        Details for <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> can be found in
        <a class="xref" href="programs.html" title="Chapter 4 MySQL Programs">Chapter 4, <i>MySQL Programs</i></a>.
      </p></li><li class="listitem"><p>
        The syntax of the SQL statements described here is given in
        <a class="xref" href="sql-syntax.html" title="Chapter 13 SQL Statement Syntax">Chapter 13, <i>SQL Statement Syntax</i></a>.
      </p></li><li class="listitem"><p>
        For additional information about <code class="literal">InnoDB</code>
        backup procedures, see <a class="xref" href="innodb-storage-engine.html#innodb-backup" title="14.18.1 InnoDB Backup">Section 14.18.1, “InnoDB Backup”</a>.
      </p></li><li class="listitem"><p>
        Replication enables you to maintain identical data on multiple
        servers. This has several benefits, such as enabling client
        query load to be distributed over servers, availability of data
        even if a given server is taken offline or fails, and the
        ability to make backups with no impact on the master by using a
        slave server. See <a class="xref" href="replication.html" title="Chapter 16 Replication">Chapter 16, <i>Replication</i></a>.
      </p></li><li class="listitem"><p>
        MySQL InnoDB cluster is a collection of products that work
        together to provide a high availability solution. A group of
        MySQL servers can be configured to create a cluster using
        MySQL Shell. The cluster of servers has a single master,
        called the primary, which acts as the read-write master.
        Multiple secondary servers are replicas of the master. A minimum
        of three servers are required to create a high availability
        cluster. A client application is connected to the primary via
        MySQL Router. If the primary fails, a secondary is automatically
        promoted to the role of primary, and MySQL Router routes requests to
        the new primary.
      </p></li><li class="listitem"><p>
        NDB Cluster provides a high-availability, high-redundancy
        version of MySQL adapted for the distributed computing
        environment. See <a class="xref" href="mysql-cluster.html" title="Chapter 21 MySQL NDB Cluster 7.5 and NDB Cluster 7.6">Chapter 21, <i>MySQL NDB Cluster 7.5 and NDB Cluster 7.6</i></a>, which provides
        information about MySQL NDB Cluster 7.5 (based on MySQL 5.7 but
        containing the latest improvements and fixes for the
        <a class="link" href="mysql-cluster.html" title="Chapter 21 MySQL NDB Cluster 7.5 and NDB Cluster 7.6"><code class="literal">NDB</code></a> storage engine).
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="backup-types"></a>7.1 Backup and Recovery Types</h2>

</div>

</div>

</div>
<p>
      This section describes the characteristics of different types of
      backups.
</p>
<h3><a name="idm140710722792160"></a>Physical (Raw) Versus Logical Backups</h3>
<p>
      Physical backups consist of raw copies of the directories and
      files that store database contents. This type of backup is
      suitable for large, important databases that need to be recovered
      quickly when problems occur.
    </p><p>
      Logical backups save information represented as logical database
      structure (<a class="link" href="sql-syntax.html#create-database" title="13.1.11 CREATE DATABASE Syntax"><code class="literal">CREATE DATABASE</code></a>,
      <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statements) and
      content (<a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> statements or
      delimited-text files). This type of backup is suitable for smaller
      amounts of data where you might edit the data values or table
      structure, or recreate the data on a different machine
      architecture.
    </p><p>
      Physical backup methods have these characteristics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          The backup consists of exact copies of database directories
          and files. Typically this is a copy of all or part of the
          MySQL data directory.
        </p></li><li class="listitem"><p>
          Physical backup methods are faster than logical because they
          involve only file copying without conversion.
        </p></li><li class="listitem"><p>
          Output is more compact than for logical backup.
        </p></li><li class="listitem"><p>
          Because backup speed and compactness are important for busy,
          important databases, the MySQL Enterprise Backup product
          performs physical backups. For an overview of the MySQL
          Enterprise Backup product, see
          <a class="xref" href="mysql-enterprise.html#mysql-enterprise-backup" title="29.2 MySQL Enterprise Backup Overview">Section 29.2, “MySQL Enterprise Backup Overview”</a>.
        </p></li><li class="listitem"><p>
          Backup and restore granularity ranges from the level of the
          entire data directory down to the level of individual files.
          This may or may not provide for table-level granularity,
          depending on storage engine. For example,
          <code class="literal">InnoDB</code> tables can each be in a separate
          file, or share file storage with other
          <code class="literal">InnoDB</code> tables; each
          <code class="literal">MyISAM</code> table corresponds uniquely to a set
          of files.
        </p></li><li class="listitem"><p>
          In addition to databases, the backup can include any related
          files such as log or configuration files.
        </p></li><li class="listitem"><p>
          Data from <code class="literal">MEMORY</code> tables is tricky to back
          up this way because their contents are not stored on disk.
          (The MySQL Enterprise Backup product has a feature where you
          can retrieve data from <code class="literal">MEMORY</code> tables during
          a backup.)
        </p></li><li class="listitem"><p>
          Backups are portable only to other machines that have
          identical or similar hardware characteristics.
        </p></li><li class="listitem"><p>
          Backups can be performed while the MySQL server is not
          running. If the server is running, it is necessary to perform
          appropriate locking so that the server does not change
          database contents during the backup. MySQL Enterprise Backup
          does this locking automatically for tables that require it.
        </p></li><li class="listitem"><p>
          Physical backup tools include the
          <span class="command"><strong>mysqlbackup</strong></span> of MySQL Enterprise Backup for
          <code class="literal">InnoDB</code> or any other tables, or file
          system-level commands (such as <span class="command"><strong>cp</strong></span>,
          <span class="command"><strong>scp</strong></span>, <span class="command"><strong>tar</strong></span>,
          <span class="command"><strong>rsync</strong></span>) for <code class="literal">MyISAM</code>
          tables.
        </p></li><li class="listitem"><p>
          For restore:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              MySQL Enterprise Backup restores <code class="literal">InnoDB</code>
              and other tables that it backed up.
            </p></li><li class="listitem"><p>
              <a class="link" href="mysql-cluster.html#mysql-cluster-programs-ndb-restore" title="21.4.24 ndb_restore — Restore an NDB Cluster Backup"><span class="command"><strong>ndb_restore</strong></span></a> restores
              <a class="link" href="mysql-cluster.html" title="Chapter 21 MySQL NDB Cluster 7.5 and NDB Cluster 7.6"><code class="literal">NDB</code></a> tables.
            </p></li><li class="listitem"><p>
              Files copied at the file system level can be copied back
              to their original locations with file system commands.
</p></li></ul>
</div>
</li></ul>
</div>
<p>
      Logical backup methods have these characteristics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          The backup is done by querying the MySQL server to obtain
          database structure and content information.
        </p></li><li class="listitem"><p>
          Backup is slower than physical methods because the server must
          access database information and convert it to logical format.
          If the output is written on the client side, the server must
          also send it to the backup program.
        </p></li><li class="listitem"><p>
          Output is larger than for physical backup, particularly when
          saved in text format.
        </p></li><li class="listitem"><p>
          Backup and restore granularity is available at the server
          level (all databases), database level (all tables in a
          particular database), or table level. This is true regardless
          of storage engine.
        </p></li><li class="listitem"><p>
          The backup does not include log or configuration files, or
          other database-related files that are not part of databases.
        </p></li><li class="listitem"><p>
          Backups stored in logical format are machine independent and
          highly portable.
        </p></li><li class="listitem"><p>
          Logical backups are performed with the MySQL server running.
          The server is not taken offline.
        </p></li><li class="listitem"><p>
          Logical backup tools include the <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a>
          program and the <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT
          ... INTO OUTFILE</code></a> statement. These work for any
          storage engine, even <code class="literal">MEMORY</code>.
        </p></li><li class="listitem"><p>
          To restore logical backups, SQL-format dump files can be
          processed using the <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> client. To load
          delimited-text files, use the
          <a class="link" href="sql-syntax.html#load-data" title="13.2.6 LOAD DATA INFILE Syntax"><code class="literal">LOAD DATA
          INFILE</code></a> statement or the
          <a class="link" href="programs.html#mysqlimport" title="4.5.5 mysqlimport — A Data Import Program"><span class="command"><strong>mysqlimport</strong></span></a> client.
</p></li></ul>
</div>
<h3><a name="idm140710722743008"></a>Online Versus Offline Backups</h3>
<p>
      Online backups take place while the MySQL server is running so
      that the database information can be obtained from the server.
      Offline backups take place while the server is stopped. This
      distinction can also be described as <span class="quote">“<span class="quote">hot</span>”</span> versus
      <span class="quote">“<span class="quote">cold</span>”</span> backups; a <span class="quote">“<span class="quote">warm</span>”</span> backup is one
      where the server remains running but locked against modifying data
      while you access database files externally.
    </p><p>
      Online backup methods have these characteristics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          The backup is less intrusive to other clients, which can
          connect to the MySQL server during the backup and may be able
          to access data depending on what operations they need to
          perform.
        </p></li><li class="listitem"><p>
          Care must be taken to impose appropriate locking so that data
          modifications do not take place that would compromise backup
          integrity. The MySQL Enterprise Backup product does such
          locking automatically.
</p></li></ul>
</div>
<p>
      Offline backup methods have these characteristics:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Clients can be affected adversely because the server is
          unavailable during backup. For that reason, such backups are
          often taken from a replication slave server that can be taken
          offline without harming availability.
        </p></li><li class="listitem"><p>
          The backup procedure is simpler because there is no
          possibility of interference from client activity.
</p></li></ul>
</div>
<p>
      A similar distinction between online and offline applies for
      recovery operations, and similar characteristics apply. However,
      it is more likely that clients will be affected for online
      recovery than for online backup because recovery requires stronger
      locking. During backup, clients might be able to read data while
      it is being backed up. Recovery modifies data and does not just
      read it, so clients must be prevented from accessing data while it
      is being restored.
</p>
<h3><a name="idm140710722733488"></a>Local Versus Remote Backups</h3>
<p>
      A local backup is performed on the same host where the MySQL
      server runs, whereas a remote backup is done from a different
      host. For some types of backups, the backup can be initiated from
      a remote host even if the output is written locally on the server.
      host.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> can connect to local or remote
          servers. For SQL output (<code class="literal">CREATE</code> and
          <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> statements), local or
          remote dumps can be done and generate output on the client.
          For delimited-text output (with the
          <a class="link" href="programs.html#option_mysqldump_tab"><code class="option">--tab</code></a> option), data files
          are created on the server host.
        </p></li><li class="listitem"><p>
          <a class="link" href="sql-syntax.html#select-into" title="13.2.9.1 SELECT ... INTO Syntax"><code class="literal">SELECT ... INTO
          OUTFILE</code></a> can be initiated from a local or remote
          client host, but the output file is created on the server
          host.
        </p></li><li class="listitem"><p>
          Physical backup methods typically are initiated locally on the
          MySQL server host so that the server can be taken offline,
          although the destination for copied files might be remote.
</p></li></ul>
</div>
<h3><a name="idm140710722723408"></a>Snapshot Backups</h3>
<p>
      Some file system implementations enable <span class="quote">“<span class="quote">snapshots</span>”</span>
      to be taken. These provide logical copies of the file system at a
      given point in time, without requiring a physical copy of the
      entire file system. (For example, the implementation may use
      copy-on-write techniques so that only parts of the file system
      modified after the snapshot time need be copied.) MySQL itself
      does not provide the capability for taking file system snapshots.
      It is available through third-party solutions such as Veritas,
      LVM, or ZFS.
</p>
<h3><a name="idm140710722721328"></a>Full Versus Incremental Backups</h3>
<p>
      A full backup includes all data managed by a MySQL server at a
      given point in time. An incremental backup consists of the changes
      made to the data during a given time span (from one point in time
      to another). MySQL has different ways to perform full backups,
      such as those described earlier in this section. Incremental
      backups are made possible by enabling the server's binary log,
      which the server uses to record data changes.
</p>
<h3><a name="idm140710722719248"></a>Full Versus Point-in-Time (Incremental) Recovery</h3>
<p>
      A full recovery restores all data from a full backup. This
      restores the server instance to the state that it had when the
      backup was made. If that state is not sufficiently current, a full
      recovery can be followed by recovery of incremental backups made
      since the full backup, to bring the server to a more up-to-date
      state.
    </p><p>
      Incremental recovery is recovery of changes made during a given
      time span. This is also called point-in-time recovery because it
      makes a server's state current up to a given time. Point-in-time
      recovery is based on the binary log and typically follows a full
      recovery from the backup files that restores the server to its
      state when the backup was made. Then the data changes written in
      the binary log files are applied as incremental recovery to redo
      data modifications and bring the server up to the desired point in
      time.
</p>
<h3><a name="idm140710722716736"></a>Table Maintenance</h3>
<p>
      Data integrity can be compromised if tables become corrupt. For
      <a class="link" href="innodb-storage-engine.html" title="Chapter 14 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> tables, this is not a typical
      issue. For programs to check <a class="link" href="storage-engines.html#myisam-storage-engine" title="15.2 The MyISAM Storage Engine"><code class="literal">MyISAM</code></a>
      tables and repair them if problems are found, see
      <a class="xref" href="backup-and-recovery.html#myisam-table-maintenance" title="7.6 MyISAM Table Maintenance and Crash Recovery">Section 7.6, “MyISAM Table Maintenance and Crash Recovery”</a>.
</p>
<h3><a name="idm140710722712256"></a>Backup Scheduling, Compression, and Encryption</h3>
<p>
      Backup scheduling is valuable for automating backup procedures.
      Compression of backup output reduces space requirements, and
      encryption of the output provides better security against
      unauthorized access of backed-up data. MySQL itself does not
      provide these capabilities. The MySQL Enterprise Backup product
      can compress <code class="literal">InnoDB</code> backups, and compression or
      encryption of backup output can be achieved using file system
      utilities. Other third-party solutions may be available.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="backup-methods"></a>7.2 Database Backup Methods</h2>

</div>

</div>

</div>
<p>
      This section summarizes some general methods for making backups.
</p>
<h3><a name="idm140710722708176"></a>Making a Hot Backup with MySQL Enterprise Backup</h3>
<p>
      Customers of MySQL Enterprise Edition can use the
      <a class="link" href="glossary.html#glos_mysql_enterprise_backup" title="MySQL Enterprise Backup">MySQL Enterprise
      Backup</a> product to do
      <a class="link" href="glossary.html#glos_physical" title="physical">physical</a> backups of entire
      instances or selected databases, tables, or both. This product
      includes features for
      <a class="link" href="glossary.html#glos_incremental_backup" title="incremental backup">incremental</a> and
      <a class="link" href="glossary.html#glos_compressed_backup" title="compressed backup">compressed</a> backups.
      Backing up the physical database files makes restore much faster
      than logical techniques such as the <code class="literal">mysqldump</code>
      command. <code class="literal">InnoDB</code> tables are copied using a
      <a class="link" href="glossary.html#glos_hot_backup" title="hot backup">hot backup</a> mechanism.
      (Ideally, the <code class="literal">InnoDB</code> tables should represent a
      substantial majority of the data.) Tables from other storage
      engines are copied using a <a class="link" href="glossary.html#glos_warm_backup" title="warm backup">warm
      backup</a> mechanism. For an overview of the MySQL Enterprise
      Backup product, see <a class="xref" href="mysql-enterprise.html#mysql-enterprise-backup" title="29.2 MySQL Enterprise Backup Overview">Section 29.2, “MySQL Enterprise Backup Overview”</a>.
</p>
<h3><a name="idm140710722697920"></a>Making Backups with mysqldump</h3>
<p>
      The <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> program can make backups. It can
      back up all kinds of tables. (See
      <a class="xref" href="backup-and-recovery.html#using-mysqldump" title="7.4 Using mysqldump for Backups">Section 7.4, “Using mysqldump for Backups”</a>.)
    </p><p>
      For <code class="literal">InnoDB</code> tables, it is possible to perform an
      online backup that takes no locks on tables using the
      <a class="link" href="programs.html#option_mysqldump_single-transaction"><code class="option">--single-transaction</code></a> option to
      <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a>. See <a class="xref" href="backup-and-recovery.html#backup-policy" title="7.3.1 Establishing a Backup Policy">Section 7.3.1, “Establishing a Backup Policy”</a>.
</p>
<h3><a name="idm140710722690576"></a>Making Backups by Copying Table Files</h3>
<p>
      For storage engines that represent each table using its own files,
      tables can be backed up by copying those files. For example,
      <code class="literal">MyISAM</code> tables are stored as files, so it is
      easy to do a backup by copying files (<code class="filename">*.frm</code>,
      <code class="filename">*.MYD</code>, and <code class="filename">*.MYI</code> files).
      To get a consistent backup, stop the server or lock and flush the
      relevant tables:
    </p><pre data-lang="sql" class="programlisting">
FLUSH TABLES <em class="replaceable"><code>tbl_list</code></em> WITH READ LOCK;
</pre><p>
      You need only a read lock; this enables other clients to continue
      to query the tables while you are making a copy of the files in
      the database directory. The flush is needed to ensure that the all
      active index pages are written to disk before you start the
      backup. See <a class="xref" href="sql-syntax.html#lock-tables" title="13.3.5 LOCK TABLES and UNLOCK TABLES Syntax">Section 13.3.5, “LOCK TABLES and UNLOCK TABLES Syntax”</a>, and
      <a class="xref" href="sql-syntax.html#flush" title="13.7.6.3 FLUSH Syntax">Section 13.7.6.3, “FLUSH Syntax”</a>.
    </p><p>
      You can also create a binary backup simply by copying all table
      files, as long as the server isn't updating anything. (But note
      that table file copying methods do not work if your database
      contains <code class="literal">InnoDB</code> tables. Also, even if the
      server is not actively updating data, <code class="literal">InnoDB</code>
      may still have modified data cached in memory and not flushed to
      disk.)
</p>
<h3><a name="idm140710722680656"></a>Making Delimited-Text File Backups</h3>
<p>
      To create a text file containing a table's data, you can use
      <a class="link" href="sql-syntax.html#select-into" title="13.2.9.1 SELECT ... INTO Syntax"><code class="literal">SELECT * INTO OUTFILE
      '<em class="replaceable"><code>file_name</code></em>' FROM
      <em class="replaceable"><code>tbl_name</code></em></code></a>. The file is created
      on the MySQL server host, not the client host. For this statement,
      the output file cannot already exist because permitting files to
      be overwritten constitutes a security risk. See
      <a class="xref" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax">Section 13.2.9, “SELECT Syntax”</a>. This method works for any kind of data
      file, but saves only table data, not the table structure.
    </p><p>
      Another way to create text data files (along with files containing
      <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statements for the
      backed up tables) is to use <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> with the
      <a class="link" href="programs.html#option_mysqldump_tab"><code class="option">--tab</code></a> option. See
      <a class="xref" href="backup-and-recovery.html#mysqldump-delimited-text" title="7.4.3 Dumping Data in Delimited-Text Format with mysqldump">Section 7.4.3, “Dumping Data in Delimited-Text Format with mysqldump”</a>.
    </p><p>
      To reload a delimited-text data file, use
      <a class="link" href="sql-syntax.html#load-data" title="13.2.6 LOAD DATA INFILE Syntax"><code class="literal">LOAD DATA
      INFILE</code></a> or <a class="link" href="programs.html#mysqlimport" title="4.5.5 mysqlimport — A Data Import Program"><span class="command"><strong>mysqlimport</strong></span></a>.
</p>
<h3><a name="idm140710722668768"></a>Making Incremental Backups by Enabling the Binary Log</h3>
<p>
      MySQL supports incremental backups: You must start the server with
      the <a class="link" href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option to enable
      binary logging; see <a class="xref" href="server-administration.html#binary-log" title="5.4.4 The Binary Log">Section 5.4.4, “The Binary Log”</a>. The binary log
      files provide you with the information you need to replicate
      changes to the database that are made subsequent to the point at
      which you performed a backup. At the moment you want to make an
      incremental backup (containing all changes that happened since the
      last full or incremental backup), you should rotate the binary log
      by using <a class="link" href="sql-syntax.html#flush-logs"><code class="literal">FLUSH LOGS</code></a>. This done, you
      need to copy to the backup location all binary logs which range
      from the one of the moment of the last full or incremental backup
      to the last but one. These binary logs are the incremental backup;
      at restore time, you apply them as explained in
      <a class="xref" href="backup-and-recovery.html#point-in-time-recovery" title="7.5 Point-in-Time (Incremental) Recovery Using the Binary Log">Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”</a>. The next time you do a
      full backup, you should also rotate the binary log using
      <a class="link" href="sql-syntax.html#flush-logs"><code class="literal">FLUSH LOGS</code></a> or <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump
      --flush-logs</strong></span></a>. See <a class="xref" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program">Section 4.5.4, “<span class="command"><strong>mysqldump</strong></span> — A Database Backup Program”</a>.
</p>
<h3><a name="idm140710722660096"></a>Making Backups Using Replication Slaves</h3>
<p>
      If you have performance problems with your master server while
      making backups, one strategy that can help is to set up
      replication and perform backups on the slave rather than on the
      master. See <a class="xref" href="replication.html#replication-solutions-backups" title="16.3.1 Using Replication for Backups">Section 16.3.1, “Using Replication for Backups”</a>.
    </p><p>
      If you are backing up a slave replication server, you should back
      up its master info and relay log info repositories (see
      <a class="xref" href="replication.html#slave-logs" title="16.2.4 Replication Relay and Status Logs">Section 16.2.4, “Replication Relay and Status Logs”</a>) when you back up the slave's
      databases, regardless of the backup method you choose. These
      information files are always needed to resume replication after
      you restore the slave's data. If your slave is replicating
      <a class="link" href="sql-syntax.html#load-data" title="13.2.6 LOAD DATA INFILE Syntax"><code class="literal">LOAD DATA
      INFILE</code></a> statements, you should also back up any
      <code class="filename">SQL_LOAD-*</code> files that exist in the directory
      that the slave uses for this purpose. The slave needs these files
      to resume replication of any interrupted
      <a class="link" href="sql-syntax.html#load-data" title="13.2.6 LOAD DATA INFILE Syntax"><code class="literal">LOAD DATA
      INFILE</code></a> operations. The location of this directory is the
      value of the <a class="link" href="replication.html#option_mysqld_slave-load-tmpdir"><code class="option">--slave-load-tmpdir</code></a>
      option. If the server was not started with that option, the
      directory location is the value of the
      <a class="link" href="server-administration.html#sysvar_tmpdir"><code class="literal">tmpdir</code></a> system variable.
</p>
<h3><a name="idm140710722650704"></a>Recovering Corrupt Tables</h3>
<p>
      If you have to restore <code class="literal">MyISAM</code> tables that have
      become corrupt, try to recover them using
      <a class="link" href="sql-syntax.html#repair-table" title="13.7.2.5 REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a> or <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk
      -r</strong></span></a> first. That should work in 99.9% of all cases. If
      <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> fails, see
      <a class="xref" href="backup-and-recovery.html#myisam-table-maintenance" title="7.6 MyISAM Table Maintenance and Crash Recovery">Section 7.6, “MyISAM Table Maintenance and Crash Recovery”</a>.
</p>
<h3><a name="idm140710722644272"></a>Making Backups Using a File System Snapshot</h3>
<p>
      If you are using a Veritas file system, you can make a backup like
      this:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
          From a client program, execute <a class="link" href="sql-syntax.html#flush-tables-with-read-lock"><code class="literal">FLUSH
          TABLES WITH READ LOCK</code></a>.
        </p></li><li class="listitem"><p>
          From another shell, execute <code class="literal">mount vxfs
          snapshot</code>.
        </p></li><li class="listitem"><p>
          From the first client, execute
          <a class="link" href="sql-syntax.html#lock-tables" title="13.3.5 LOCK TABLES and UNLOCK TABLES Syntax"><code class="literal">UNLOCK
          TABLES</code></a>.
        </p></li><li class="listitem"><p>
          Copy files from the snapshot.
        </p></li><li class="listitem"><p>
          Unmount the snapshot.
</p></li></ol>
</div>
<p>
      Similar snapshot capabilities may be available in other file
      systems, such as LVM or ZFS.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="backup-strategy-example"></a>7.3 Example Backup and Recovery Strategy</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="backup-and-recovery.html#backup-policy">7.3.1 Establishing a Backup Policy</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#recovery-from-backups">7.3.2 Using Backups for Recovery</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#backup-strategy-summary">7.3.3 Backup Strategy Summary</a></span></dt></dl>
</div>
<p>
      This section discusses a procedure for performing backups that
      enables you to recover data after several types of crashes:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Operating system crash
        </p></li><li class="listitem"><p>
          Power failure
        </p></li><li class="listitem"><p>
          File system crash
        </p></li><li class="listitem"><p>
          Hardware problem (hard drive, motherboard, and so forth)
</p></li></ul>
</div>
<p>
      The example commands do not include options such as
      <a class="link" href="programs.html#option_general_user"><code class="option">--user</code></a> and
      <a class="link" href="programs.html#option_general_password"><code class="option">--password</code></a> for the
      <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> and <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> client
      programs. You should include such options as necessary to enable
      client programs to connect to the MySQL server.
    </p><p>
      Assume that data is stored in the <code class="literal">InnoDB</code>
      storage engine, which has support for transactions and automatic
      crash recovery. Assume also that the MySQL server is under load at
      the time of the crash. If it were not, no recovery would ever be
      needed.
    </p><p>
      For cases of operating system crashes or power failures, we can
      assume that MySQL's disk data is available after a restart. The
      <code class="literal">InnoDB</code> data files might not contain consistent
      data due to the crash, but <code class="literal">InnoDB</code> reads its
      logs and finds in them the list of pending committed and
      noncommitted transactions that have not been flushed to the data
      files. <code class="literal">InnoDB</code> automatically rolls back those
      transactions that were not committed, and flushes to its data
      files those that were committed. Information about this recovery
      process is conveyed to the user through the MySQL error log. The
      following is an example log excerpt:
    </p><pre data-lang="simple" class="programlisting">
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections
</pre><p>
      For the cases of file system crashes or hardware problems, we can
      assume that the MySQL disk data is <span class="emphasis"><em>not</em></span>
      available after a restart. This means that MySQL fails to start
      successfully because some blocks of disk data are no longer
      readable. In this case, it is necessary to reformat the disk,
      install a new one, or otherwise correct the underlying problem.
      Then it is necessary to recover our MySQL data from backups, which
      means that backups must already have been made. To make sure that
      is the case, design and implement a backup policy.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="backup-policy"></a>7.3.1 Establishing a Backup Policy</h3>
</div>
</div>
</div>
<p>
        To be useful, backups must be scheduled regularly. A full backup
        (a snapshot of the data at a point in time) can be done in MySQL
        with several tools. For example,
        <a class="link" href="mysql-enterprise.html#mysql-enterprise-backup" title="29.2 MySQL Enterprise Backup Overview">MySQL Enterprise
        Backup</a> can perform a
        <a class="link" href="glossary.html#glos_physical_backup" title="physical backup">physical backup</a> of
        an entire instance, with optimizations to minimize overhead and
        avoid disruption when backing up <code class="literal">InnoDB</code> data
        files; <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> provides online
        <a class="link" href="glossary.html#glos_logical_backup" title="logical backup">logical backup</a>. This
        discussion uses <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a>.
      </p><p>
        Assume that we make a full backup of all our
        <code class="literal">InnoDB</code> tables in all databases using the
        following command on Sunday at 1 p.m., when load is low:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --all-databases --master-data --single-transaction &gt; backup_sunday_1_PM.sql</code></strong>
</pre><p>
        The resulting <code class="filename">.sql</code> file produced by
        <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> contains a set of SQL
        <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> statements that can be
        used to reload the dumped tables at a later time.
      </p><p>
        This backup operation acquires a global read lock on all tables
        at the beginning of the dump (using <a class="link" href="sql-syntax.html#flush-tables-with-read-lock"><code class="literal">FLUSH
        TABLES WITH READ LOCK</code></a>). As soon as this lock has been
        acquired, the binary log coordinates are read and the lock is
        released. If long updating statements are running when the
        <a class="link" href="sql-syntax.html#flush" title="13.7.6.3 FLUSH Syntax"><code class="literal">FLUSH</code></a> statement is issued, the
        backup operation may stall until those statements finish. After
        that, the dump becomes lock-free and does not disturb reads and
        writes on the tables.
      </p><p>
        It was assumed earlier that the tables to back up are
        <code class="literal">InnoDB</code> tables, so
        <a class="link" href="programs.html#option_mysqldump_single-transaction"><code class="option">--single-transaction</code></a> uses a
        consistent read and guarantees that data seen by
        <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> does not change. (Changes made by
        other clients to <code class="literal">InnoDB</code> tables are not seen
        by the <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> process.) If the backup
        operation includes nontransactional tables, consistency requires
        that they do not change during the backup. For example, for the
        <code class="literal">MyISAM</code> tables in the <code class="literal">mysql</code>
        database, there must be no administrative changes to MySQL
        accounts during the backup.
      </p><p>
        Full backups are necessary, but it is not always convenient to
        create them. They produce large backup files and take time to
        generate. They are not optimal in the sense that each successive
        full backup includes all data, even that part that has not
        changed since the previous full backup. It is more efficient to
        make an initial full backup, and then to make incremental
        backups. The incremental backups are smaller and take less time
        to produce. The tradeoff is that, at recovery time, you cannot
        restore your data just by reloading the full backup. You must
        also process the incremental backups to recover the incremental
        changes.
      </p><p>
        To make incremental backups, we need to save the incremental
        changes. In MySQL, these changes are represented in the binary
        log, so the MySQL server should always be started with the
        <a class="link" href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option to enable that
        log. With binary logging enabled, the server writes each data
        change into a file while it updates data. Looking at the data
        directory of a MySQL server that was started with the
        <a class="link" href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option and that has
        been running for some days, we find these MySQL binary log
        files:
      </p><pre data-lang="simple" class="programlisting">
-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index
</pre><p>
        Each time it restarts, the MySQL server creates a new binary log
        file using the next number in the sequence. While the server is
        running, you can also tell it to close the current binary log
        file and begin a new one manually by issuing a
        <a class="link" href="sql-syntax.html#flush-logs"><code class="literal">FLUSH LOGS</code></a> SQL statement or with
        a <a class="link" href="programs.html#mysqladmin" title="4.5.2 mysqladmin — Client for Administering a MySQL Server"><span class="command"><strong>mysqladmin flush-logs</strong></span></a> command.
        <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> also has an option to flush the
        logs. The <code class="literal">.index</code> file in the data directory
        contains the list of all MySQL binary logs in the directory.
      </p><p>
        The MySQL binary logs are important for recovery because they
        form the set of incremental backups. If you make sure to flush
        the logs when you make your full backup, the binary log files
        created afterward contain all the data changes made since the
        backup. Let's modify the previous <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a>
        command a bit so that it flushes the MySQL binary logs at the
        moment of the full backup, and so that the dump file contains
        the name of the new current binary log:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --single-transaction --flush-logs --master-data=2 \</code></strong>
         <strong class="userinput"><code>--all-databases &gt; backup_sunday_1_PM.sql</code></strong>
</pre><p>
        After executing this command, the data directory contains a new
        binary log file, <code class="filename">gbichot2-bin.000007</code>,
        because the <a class="link" href="programs.html#option_mysqldump_flush-logs"><code class="option">--flush-logs</code></a>
        option causes the server to flush its logs. The
        <a class="link" href="programs.html#option_mysqldump_master-data"><code class="option">--master-data</code></a> option causes
        <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> to write binary log information to
        its output, so the resulting <code class="filename">.sql</code> dump file
        includes these lines:
      </p><pre data-lang="simple" class="programlisting">
-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;
</pre><p>
        Because the <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> command made a full
        backup, those lines mean two things:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            The dump file contains all changes made before any changes
            written to the <code class="filename">gbichot2-bin.000007</code>
            binary log file or higher.
          </p></li><li class="listitem"><p>
            All data changes logged after the backup are not present in
            the dump file, but are present in the
            <code class="filename">gbichot2-bin.000007</code> binary log file or
            higher.
</p></li></ul>
</div>
<p>
        On Monday at 1 p.m., we can create an incremental backup by
        flushing the logs to begin a new binary log file. For example,
        executing a <a class="link" href="programs.html#mysqladmin" title="4.5.2 mysqladmin — Client for Administering a MySQL Server"><span class="command"><strong>mysqladmin flush-logs</strong></span></a> command
        creates <code class="filename">gbichot2-bin.000008</code>. All changes
        between the Sunday 1 p.m. full backup and Monday 1 p.m. will be
        in the <code class="filename">gbichot2-bin.000007</code> file. This
        incremental backup is important, so it is a good idea to copy it
        to a safe place. (For example, back it up on tape or DVD, or
        copy it to another machine.) On Tuesday at 1 p.m., execute
        another <a class="link" href="programs.html#mysqladmin" title="4.5.2 mysqladmin — Client for Administering a MySQL Server"><span class="command"><strong>mysqladmin flush-logs</strong></span></a> command. All
        changes between Monday 1 p.m. and Tuesday 1 p.m. will be in the
        <code class="filename">gbichot2-bin.000008</code> file (which also should
        be copied somewhere safe).
      </p><p>
        The MySQL binary logs take up disk space. To free up space,
        purge them from time to time. One way to do this is by deleting
        the binary logs that are no longer needed, such as when we make
        a full backup:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --single-transaction --flush-logs --master-data=2 \</code></strong>
         <strong class="userinput"><code>--all-databases --delete-master-logs &gt; backup_sunday_1_PM.sql</code></strong>
</pre>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
          Deleting the MySQL binary logs with <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump
          --delete-master-logs</strong></span></a> can be dangerous if your server
          is a replication master server, because slave servers might
          not yet fully have processed the contents of the binary log.
          The description for the <a class="link" href="sql-syntax.html#purge-binary-logs" title="13.4.1.1 PURGE BINARY LOGS Syntax"><code class="literal">PURGE BINARY
          LOGS</code></a> statement explains what should be verified
          before deleting the MySQL binary logs. See
          <a class="xref" href="sql-syntax.html#purge-binary-logs" title="13.4.1.1 PURGE BINARY LOGS Syntax">Section 13.4.1.1, “PURGE BINARY LOGS Syntax”</a>.
</p>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="recovery-from-backups"></a>7.3.2 Using Backups for Recovery</h3>

</div>

</div>

</div>
<p>
        Now, suppose that we have a catastrophic crash on Wednesday at 8
        a.m. that requires recovery from backups. To recover, first we
        restore the last full backup we have (the one from Sunday 1
        p.m.). The full backup file is just a set of SQL statements, so
        restoring it is very easy:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql &lt; backup_sunday_1_PM.sql</code></strong>
</pre><p>
        At this point, the data is restored to its state as of Sunday 1
        p.m.. To restore the changes made since then, we must use the
        incremental backups; that is, the
        <code class="filename">gbichot2-bin.000007</code> and
        <code class="filename">gbichot2-bin.000008</code> binary log files. Fetch
        the files if necessary from where they were backed up, and then
        process their contents like this:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql</code></strong>
</pre><p>
        We now have recovered the data to its state as of Tuesday 1
        p.m., but still are missing the changes from that date to the
        date of the crash. To not lose them, we would have needed to
        have the MySQL server store its MySQL binary logs into a safe
        location (RAID disks, SAN, ...) different from the place where
        it stores its data files, so that these logs were not on the
        destroyed disk. (That is, we can start the server with a
        <a class="link" href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option that specifies a
        location on a different physical device from the one on which
        the data directory resides. That way, the logs are safe even if
        the device containing the directory is lost.) If we had done
        this, we would have the <code class="filename">gbichot2-bin.000009</code>
        file (and any subsequent files) at hand, and we could apply them
        using <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a> and
        <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> to restore the most recent data changes
        with no loss up to the moment of the crash:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog gbichot2-bin.000009 ... | mysql</code></strong>
</pre><p>
        For more information about using <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a>
        to process binary log files, see
        <a class="xref" href="backup-and-recovery.html#point-in-time-recovery" title="7.5 Point-in-Time (Incremental) Recovery Using the Binary Log">Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”</a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="backup-strategy-summary"></a>7.3.3 Backup Strategy Summary</h3>

</div>

</div>

</div>
<p>
        In case of an operating system crash or power failure,
        <code class="literal">InnoDB</code> itself does all the job of recovering
        data. But to make sure that you can sleep well, observe the
        following guidelines:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            Always run the MySQL server with the
            <a class="link" href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option, or even
            <a class="link" href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin=<em class="replaceable"><code>log_name</code></em></code></a>,
            where the log file name is located on some safe media
            different from the drive on which the data directory is
            located. If you have such safe media, this technique can
            also be good for disk load balancing (which results in a
            performance improvement).
          </p></li><li class="listitem"><p>
            Make periodic full backups, using the
            <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> command shown earlier in
            <a class="xref" href="backup-and-recovery.html#backup-policy" title="7.3.1 Establishing a Backup Policy">Section 7.3.1, “Establishing a Backup Policy”</a>, that makes an online,
            nonblocking backup.
          </p></li><li class="listitem"><p>
            Make periodic incremental backups by flushing the logs with
            <a class="link" href="sql-syntax.html#flush-logs"><code class="literal">FLUSH LOGS</code></a> or
            <a class="link" href="programs.html#mysqladmin" title="4.5.2 mysqladmin — Client for Administering a MySQL Server"><span class="command"><strong>mysqladmin flush-logs</strong></span></a>.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="using-mysqldump"></a>7.4 Using mysqldump for Backups</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="backup-and-recovery.html#mysqldump-sql-format">7.4.1 Dumping Data in SQL Format with mysqldump</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#reloading-sql-format-dumps">7.4.2 Reloading SQL-Format Backups</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#mysqldump-delimited-text">7.4.3 Dumping Data in Delimited-Text Format with mysqldump</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#reloading-delimited-text-dumps">7.4.4 Reloading Delimited-Text Format Backups</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#mysqldump-tips">7.4.5 mysqldump Tips</a></span></dt></dl>
</div>
<a class="indexterm" name="idm140710722518320"></a><a class="indexterm" name="idm140710722516864"></a><p>
      This section describes how to use <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> to
      produce dump files, and how to reload dump files. A dump file can
      be used in several ways:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          As a backup to enable data recovery in case of data loss.
        </p></li><li class="listitem"><p>
          As a source of data for setting up replication slaves.
        </p></li><li class="listitem"><p>
          As a source of data for experimentation:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              To make a copy of a database that you can use without
              changing the original data.
            </p></li><li class="listitem"><p>
              To test potential upgrade incompatibilities.
</p></li></ul>
</div>
</li></ul>
</div>
<p>
      <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> produces two types of output,
      depending on whether the <a class="link" href="programs.html#option_mysqldump_tab"><code class="option">--tab</code></a>
      option is given:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Without <a class="link" href="programs.html#option_mysqldump_tab"><code class="option">--tab</code></a>,
          <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> writes SQL statements to the
          standard output. This output consists of
          <code class="literal">CREATE</code> statements to create dumped objects
          (databases, tables, stored routines, and so forth), and
          <code class="literal">INSERT</code> statements to load data into tables.
          The output can be saved in a file and reloaded later using
          <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> to recreate the dumped objects.
          Options are available to modify the format of the SQL
          statements, and to control which objects are dumped.
        </p></li><li class="listitem"><p>
          With <a class="link" href="programs.html#option_mysqldump_tab"><code class="option">--tab</code></a>,
          <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> produces two output files for
          each dumped table. The server writes one file as tab-delimited
          text, one line per table row. This file is named
          <code class="filename"><em class="replaceable"><code>tbl_name</code></em>.txt</code>
          in the output directory. The server also sends a
          <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement for the
          table to <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a>, which writes it as a
          file named
          <code class="filename"><em class="replaceable"><code>tbl_name</code></em>.sql</code>
          in the output directory.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysqldump-sql-format"></a>7.4.1 Dumping Data in SQL Format with mysqldump</h3>

</div>

</div>

</div>
<p>
        This section describes how to use <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a>
        to create SQL-format dump files. For information about reloading
        such dump files, see
        <a class="xref" href="backup-and-recovery.html#reloading-sql-format-dumps" title="7.4.2 Reloading SQL-Format Backups">Section 7.4.2, “Reloading SQL-Format Backups”</a>.
      </p><p>
        By default, <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> writes information as
        SQL statements to the standard output. You can save the output
        in a file:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump [<em class="replaceable"><code>arguments</code></em>] &gt; <em class="replaceable"><code>file_name</code></em></code></strong>
</pre><p>
        To dump all databases, invoke <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> with
        the <a class="link" href="programs.html#option_mysqldump_all-databases"><code class="option">--all-databases</code></a> option:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --all-databases &gt; dump.sql</code></strong>
</pre><p>
        To dump only specific databases, name them on the command line
        and use the <a class="link" href="programs.html#option_mysqldump_databases"><code class="option">--databases</code></a>
        option:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --databases db1 db2 db3 &gt; dump.sql</code></strong>
</pre><p>
        The <a class="link" href="programs.html#option_mysqldump_databases"><code class="option">--databases</code></a> option causes
        all names on the command line to be treated as database names.
        Without this option, <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> treats the
        first name as a database name and those following as table
        names.
      </p><p>
        With <a class="link" href="programs.html#option_mysqldump_all-databases"><code class="option">--all-databases</code></a> or
        <a class="link" href="programs.html#option_mysqldump_databases"><code class="option">--databases</code></a>,
        <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> writes <a class="link" href="sql-syntax.html#create-database" title="13.1.11 CREATE DATABASE Syntax"><code class="literal">CREATE
        DATABASE</code></a> and <a class="link" href="sql-syntax.html#use" title="13.8.4 USE Syntax"><code class="literal">USE</code></a>
        statements prior to the dump output for each database. This
        ensures that when the dump file is reloaded, it creates each
        database if it does not exist and makes it the default database
        so database contents are loaded into the same database from
        which they came. If you want to cause the dump file to force a
        drop of each database before recreating it, use the
        <a class="link" href="programs.html#option_mysqldump_add-drop-database"><code class="option">--add-drop-database</code></a> option as
        well. In this case, <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> writes a
        <a class="link" href="sql-syntax.html#drop-database" title="13.1.22 DROP DATABASE Syntax"><code class="literal">DROP DATABASE</code></a> statement preceding
        each <a class="link" href="sql-syntax.html#create-database" title="13.1.11 CREATE DATABASE Syntax"><code class="literal">CREATE DATABASE</code></a> statement.
      </p><p>
        To dump a single database, name it on the command line:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --databases test &gt; dump.sql</code></strong>
</pre><p>
        In the single-database case, it is permissible to omit the
        <a class="link" href="programs.html#option_mysqldump_databases"><code class="option">--databases</code></a> option:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump test &gt; dump.sql</code></strong>
</pre><p>
        The difference between the two preceding commands is that
        without <a class="link" href="programs.html#option_mysqldump_databases"><code class="option">--databases</code></a>, the dump
        output contains no <a class="link" href="sql-syntax.html#create-database" title="13.1.11 CREATE DATABASE Syntax"><code class="literal">CREATE
        DATABASE</code></a> or <a class="link" href="sql-syntax.html#use" title="13.8.4 USE Syntax"><code class="literal">USE</code></a>
        statements. This has several implications:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            When you reload the dump file, you must specify a default
            database name so that the server knows which database to
            reload.
          </p></li><li class="listitem"><p>
            For reloading, you can specify a database name different
            from the original name, which enables you to reload the data
            into a different database.
          </p></li><li class="listitem"><p>
            If the database to be reloaded does not exist, you must
            create it first.
          </p></li><li class="listitem"><p>
            Because the output will contain no
            <a class="link" href="sql-syntax.html#create-database" title="13.1.11 CREATE DATABASE Syntax"><code class="literal">CREATE DATABASE</code></a> statement,
            the <a class="link" href="programs.html#option_mysqldump_add-drop-database"><code class="option">--add-drop-database</code></a>
            option has no effect. If you use it, it produces no
            <a class="link" href="sql-syntax.html#drop-database" title="13.1.22 DROP DATABASE Syntax"><code class="literal">DROP DATABASE</code></a> statement.
</p></li></ul>
</div>
<p>
        To dump only specific tables from a database, name them on the
        command line following the database name:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump test t1 t3 t7 &gt; dump.sql</code></strong>
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="reloading-sql-format-dumps"></a>7.4.2 Reloading SQL-Format Backups</h3>

</div>

</div>

</div>
<p>
        To reload a dump file written by <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a>
        that consists of SQL statements, use it as input to the
        <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> client. If the dump file was created by
        <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> with the
        <a class="link" href="programs.html#option_mysqldump_all-databases"><code class="option">--all-databases</code></a> or
        <a class="link" href="programs.html#option_mysqldump_databases"><code class="option">--databases</code></a> option, it
        contains <a class="link" href="sql-syntax.html#create-database" title="13.1.11 CREATE DATABASE Syntax"><code class="literal">CREATE DATABASE</code></a> and
        <a class="link" href="sql-syntax.html#use" title="13.8.4 USE Syntax"><code class="literal">USE</code></a> statements and it is not
        necessary to specify a default database into which to load the
        data:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql &lt; dump.sql</code></strong>
</pre><p>
        Alternatively, from within <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a>, use a
        <code class="literal">source</code> command:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>source dump.sql</code></strong>
</pre><p>
        If the file is a single-database dump not containing
        <a class="link" href="sql-syntax.html#create-database" title="13.1.11 CREATE DATABASE Syntax"><code class="literal">CREATE DATABASE</code></a> and
        <a class="link" href="sql-syntax.html#use" title="13.8.4 USE Syntax"><code class="literal">USE</code></a> statements, create the
        database first (if necessary):
      </p><pre data-lang="sql" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqladmin create db1</code></strong>
</pre><p>
        Then specify the database name when you load the dump file:
      </p><pre data-lang="sql" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql db1 &lt; dump.sql</code></strong>
</pre><p>
        Alternatively, from within <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a>, create the
        database, select it as the default database, and load the dump
        file:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE DATABASE IF NOT EXISTS db1;</code></strong>
mysql&gt; <strong class="userinput"><code>USE db1;</code></strong>
mysql&gt; <strong class="userinput"><code>source dump.sql</code></strong>
</pre>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
          For Windows PowerShell users: Because the "&lt;" character is
          reserved for future use in PowerShell, an alternative approach
          is required, such as using quotes <code class="literal">cmd.exe /c "mysql
          &lt; dump.sql"</code>.
</p>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysqldump-delimited-text"></a>7.4.3 Dumping Data in Delimited-Text Format with mysqldump</h3>

</div>

</div>

</div>
<p>
        This section describes how to use <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a>
        to create delimited-text dump files. For information about
        reloading such dump files, see
        <a class="xref" href="backup-and-recovery.html#reloading-delimited-text-dumps" title="7.4.4 Reloading Delimited-Text Format Backups">Section 7.4.4, “Reloading Delimited-Text Format Backups”</a>.
      </p><p>
        If you invoke <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> with the
        <a class="link" href="programs.html#option_mysqldump_tab"><code class="option">--tab=<em class="replaceable"><code>dir_name</code></em></code></a>
        option, it uses <em class="replaceable"><code>dir_name</code></em> as the
        output directory and dumps tables individually in that directory
        using two files for each table. The table name is the base name
        for these files. For a table named <code class="literal">t1</code>, the
        files are named <code class="filename">t1.sql</code> and
        <code class="filename">t1.txt</code>. The <code class="filename">.sql</code> file
        contains a <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement
        for the table. The <code class="filename">.txt</code> file contains the
        table data, one line per table row.
      </p><p>
        The following command dumps the contents of the
        <code class="literal">db1</code> database to files in the
        <code class="filename">/tmp</code> database:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --tab=/tmp db1</code></strong>
</pre><p>
        The <code class="filename">.txt</code> files containing table data are
        written by the server, so they are owned by the system account
        used for running the server. The server uses
        <a class="link" href="sql-syntax.html#select-into" title="13.2.9.1 SELECT ... INTO Syntax"><code class="literal">SELECT ... INTO
        OUTFILE</code></a> to write the files, so you must have the
        <a class="link" href="security.html#priv_file"><code class="literal">FILE</code></a> privilege to perform this
        operation, and an error occurs if a given
        <code class="filename">.txt</code> file already exists.
      </p><p>
        The server sends the <code class="literal">CREATE</code> definitions for
        dumped tables to <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a>, which writes them
        to <code class="filename">.sql</code> files. These files therefore are
        owned by the user who executes <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a>.
      </p><p>
        It is best that <a class="link" href="programs.html#option_mysqldump_tab"><code class="option">--tab</code></a> be used
        only for dumping a local server. If you use it with a remote
        server, the <a class="link" href="programs.html#option_mysqldump_tab"><code class="option">--tab</code></a> directory
        must exist on both the local and remote hosts, and the
        <code class="filename">.txt</code> files will be written by the server in
        the remote directory (on the server host), whereas the
        <code class="filename">.sql</code> files will be written by
        <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> in the local directory (on the
        client host).
      </p><p>
        For <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump --tab</strong></span></a>, the server by default
        writes table data to <code class="filename">.txt</code> files one line
        per row with tabs between column values, no quotation marks
        around column values, and newline as the line terminator. (These
        are the same defaults as for
        <a class="link" href="sql-syntax.html#select-into" title="13.2.9.1 SELECT ... INTO Syntax"><code class="literal">SELECT ... INTO
        OUTFILE</code></a>.)
      </p><p>
        To enable data files to be written using a different format,
        <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> supports these options:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <a class="link" href="programs.html#option_mysqldump_fields"><code class="option">--fields-terminated-by=<em class="replaceable"><code>str</code></em></code></a>
          </p><p>
            The string for separating column values (default: tab).
          </p></li><li class="listitem"><p>
            <a class="link" href="programs.html#option_mysqldump_fields"><code class="option">--fields-enclosed-by=<em class="replaceable"><code>char</code></em></code></a>
          </p><p>
            The character within which to enclose column values
            (default: no character).
          </p></li><li class="listitem"><p>
            <a class="link" href="programs.html#option_mysqldump_fields"><code class="option">--fields-optionally-enclosed-by=<em class="replaceable"><code>char</code></em></code></a>
          </p><p>
            The character within which to enclose non-numeric column
            values (default: no character).
          </p></li><li class="listitem"><p>
            <a class="link" href="programs.html#option_mysqldump_fields"><code class="option">--fields-escaped-by=<em class="replaceable"><code>char</code></em></code></a>
          </p><p>
            The character for escaping special characters (default: no
            escaping).
          </p></li><li class="listitem"><p>
            <a class="link" href="programs.html#option_mysqldump_lines-terminated-by"><code class="option">--lines-terminated-by=<em class="replaceable"><code>str</code></em></code></a>
          </p><p>
            The line-termination string (default: newline).
</p></li></ul>
</div>
<p>
        Depending on the value you specify for any of these options, it
        might be necessary on the command line to quote or escape the
        value appropriately for your command interpreter. Alternatively,
        specify the value using hex notation. Suppose that you want
        <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> to quote column values within
        double quotation marks. To do so, specify double quote as the
        value for the
        <a class="link" href="programs.html#option_mysqldump_fields"><code class="option">--fields-enclosed-by</code></a>
        option. But this character is often special to command
        interpreters and must be treated specially. For example, on
        Unix, you can quote the double quote like this:
      </p><pre data-lang="simple" class="programlisting">
--fields-enclosed-by='"'
</pre><p>
        On any platform, you can specify the value in hex:
      </p><pre data-lang="simple" class="programlisting">
--fields-enclosed-by=0x22
</pre><p>
        It is common to use several of the data-formatting options
        together. For example, to dump tables in comma-separated values
        format with lines terminated by carriage-return/newline pairs
        (<code class="literal">\r\n</code>), use this command (enter it on a
        single line):
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --tab=/tmp --fields-terminated-by=,</code></strong>
         <strong class="userinput"><code>--fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1</code></strong>
</pre><p>
        Should you use any of the data-formatting options to dump table
        data, you will need to specify the same format when you reload
        data files later, to ensure proper interpretation of the file
        contents.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="reloading-delimited-text-dumps"></a>7.4.4 Reloading Delimited-Text Format Backups</h3>

</div>

</div>

</div>
<p>
        For backups produced with <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump --tab</strong></span></a>,
        each table is represented in the output directory by an
        <code class="filename">.sql</code> file containing the
        <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a> statement for the
        table, and a <code class="filename">.txt</code> file containing the table
        data. To reload a table, first change location into the output
        directory. Then process the <code class="filename">.sql</code> file with
        <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> to create an empty table and process
        the <code class="filename">.txt</code> file to load the data into the
        table:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql db1 &lt; t1.sql</code></strong>
shell&gt; <strong class="userinput"><code>mysqlimport db1 t1.txt</code></strong>
</pre><p>
        An alternative to using <a class="link" href="programs.html#mysqlimport" title="4.5.5 mysqlimport — A Data Import Program"><span class="command"><strong>mysqlimport</strong></span></a> to load
        the data file is to use the
        <a class="link" href="sql-syntax.html#load-data" title="13.2.6 LOAD DATA INFILE Syntax"><code class="literal">LOAD DATA
        INFILE</code></a> statement from within the
        <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> client:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>USE db1;</code></strong>
mysql&gt; <strong class="userinput"><code>LOAD DATA INFILE 't1.txt' INTO TABLE t1;</code></strong>
</pre><p>
        If you used any data-formatting options with
        <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> when you initially dumped the
        table, you must use the same options with
        <a class="link" href="programs.html#mysqlimport" title="4.5.5 mysqlimport — A Data Import Program"><span class="command"><strong>mysqlimport</strong></span></a> or
        <a class="link" href="sql-syntax.html#load-data" title="13.2.6 LOAD DATA INFILE Syntax"><code class="literal">LOAD DATA
        INFILE</code></a> to ensure proper interpretation of the data
        file contents:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlimport --fields-terminated-by=,</code></strong>
         <strong class="userinput"><code>--fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt</code></strong>
</pre><p>
        Or:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>USE db1;</code></strong>
mysql&gt; <strong class="userinput"><code>LOAD DATA INFILE 't1.txt' INTO TABLE t1</code></strong>
    -&gt; <strong class="userinput"><code>FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"'</code></strong>
    -&gt; <strong class="userinput"><code>LINES TERMINATED BY '\r\n';</code></strong>
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="mysqldump-tips"></a>7.4.5 mysqldump Tips</h3>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="backup-and-recovery.html#mysqldump-copying-database">7.4.5.1 Making a Copy of a Database</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#mysqldump-copying-to-other-server">7.4.5.2 Copy a Database from one Server to Another</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#mysqldump-stored-programs">7.4.5.3 Dumping Stored Programs</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#mysqldump-definition-data-dumps">7.4.5.4 Dumping Table Definitions and Content Separately</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#mysqldump-upgrade-testing">7.4.5.5 Using mysqldump to Test for Upgrade Incompatibilities</a></span></dt></dl>
</div>
<p>
        This section surveys techniques that enable you to use
        <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> to solve specific problems:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            How to make a copy a database
          </p></li><li class="listitem"><p>
            How to copy a database from one server to another
          </p></li><li class="listitem"><p>
            How to dump stored programs (stored procedures and
            functions, triggers, and events)
          </p></li><li class="listitem"><p>
            How to dump definitions and data separately
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysqldump-copying-database"></a>7.4.5.1 Making a Copy of a Database</h4>

</div>

</div>

</div>
<pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump db1 &gt; dump.sql</code></strong>
shell&gt; <strong class="userinput"><code>mysqladmin create db2</code></strong>
shell&gt; <strong class="userinput"><code>mysql db2 &lt; dump.sql</code></strong>
</pre><p>
          Do not use <a class="link" href="programs.html#option_mysqldump_databases"><code class="option">--databases</code></a> on
          the <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> command line because that
          causes <code class="literal">USE db1</code> to be included in the dump
          file, which overrides the effect of naming
          <code class="literal">db2</code> on the <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> command
          line.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysqldump-copying-to-other-server"></a>7.4.5.2 Copy a Database from one Server to Another</h4>

</div>

</div>

</div>
<p>
          On Server 1:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --databases db1 &gt; dump.sql</code></strong>
</pre><p>
          Copy the dump file from Server 1 to Server 2.
        </p><p>
          On Server 2:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql &lt; dump.sql</code></strong>
</pre><p>
          Use of <a class="link" href="programs.html#option_mysqldump_databases"><code class="option">--databases</code></a> with the
          <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> command line causes the dump file
          to include <a class="link" href="sql-syntax.html#create-database" title="13.1.11 CREATE DATABASE Syntax"><code class="literal">CREATE DATABASE</code></a> and
          <a class="link" href="sql-syntax.html#use" title="13.8.4 USE Syntax"><code class="literal">USE</code></a> statements that create the
          database if it does exist and make it the default database for
          the reloaded data.
        </p><p>
          Alternatively, you can omit
          <a class="link" href="programs.html#option_mysqldump_databases"><code class="option">--databases</code></a> from the
          <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> command. Then you will need to
          create the database on Server 2 (if necessary) and specify it
          as the default database when you reload the dump file.
        </p><p>
          On Server 1:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump db1 &gt; dump.sql</code></strong>
</pre><p>
          On Server 2:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqladmin create db1</code></strong>
shell&gt; <strong class="userinput"><code>mysql db1 &lt; dump.sql</code></strong>
</pre><p>
          You can specify a different database name in this case, so
          omitting <a class="link" href="programs.html#option_mysqldump_databases"><code class="option">--databases</code></a> from
          the <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> command enables you to dump
          data from one database and load it into another.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysqldump-stored-programs"></a>7.4.5.3 Dumping Stored Programs</h4>

</div>

</div>

</div>
<p>
          Several options control how <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a>
          handles stored programs (stored procedures and functions,
          triggers, and events):
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              <a class="link" href="programs.html#option_mysqldump_events"><code class="option">--events</code></a>: Dump Event
              Scheduler events
            </p></li><li class="listitem"><p>
              <a class="link" href="programs.html#option_mysqldump_routines"><code class="option">--routines</code></a>: Dump stored
              procedures and functions
            </p></li><li class="listitem"><p>
              <a class="link" href="programs.html#option_mysqldump_triggers"><code class="option">--triggers</code></a>: Dump
              triggers for tables
</p></li></ul>
</div>
<p>
          The <a class="link" href="programs.html#option_mysqldump_triggers"><code class="option">--triggers</code></a> option is
          enabled by default so that when tables are dumped, they are
          accompanied by any triggers they have. The other options are
          disabled by default and must be specified explicitly to dump
          the corresponding objects. To disable any of these options
          explicitly, use its skip form:
          <a class="link" href="programs.html#option_mysqldump_events"><code class="option">--skip-events</code></a>,
          <a class="link" href="programs.html#option_mysqldump_routines"><code class="option">--skip-routines</code></a>,
          or
          <a class="link" href="programs.html#option_mysqldump_triggers"><code class="option">--skip-triggers</code></a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysqldump-definition-data-dumps"></a>7.4.5.4 Dumping Table Definitions and Content Separately</h4>

</div>

</div>

</div>
<p>
          The <a class="link" href="programs.html#option_mysqldump_no-data"><code class="option">--no-data</code></a> option tells
          <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> not to dump table data, resulting
          in the dump file containing only statements to create the
          tables. Conversely, the
          <a class="link" href="programs.html#option_mysqldump_no-create-info"><code class="option">--no-create-info</code></a> option
          tells <a class="link" href="programs.html#mysqldump" title="4.5.4 mysqldump — A Database Backup Program"><span class="command"><strong>mysqldump</strong></span></a> to suppress
          <code class="literal">CREATE</code> statements from the output, so that
          the dump file contains only table data.
        </p><p>
          For example, to dump table definitions and data separately for
          the <code class="literal">test</code> database, use these commands:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --no-data test &gt; dump-defs.sql</code></strong>
shell&gt; <strong class="userinput"><code>mysqldump --no-create-info test &gt; dump-data.sql</code></strong>
</pre><p>
          For a definition-only dump, add the
          <a class="link" href="programs.html#option_mysqldump_routines"><code class="option">--routines</code></a>
          and
          <a class="link" href="programs.html#option_mysqldump_events"><code class="option">--events</code></a>
          options to also include stored routine and event definitions:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --no-data --routines --events test &gt; dump-defs.sql</code></strong>
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h4 class="title"><a name="mysqldump-upgrade-testing"></a>7.4.5.5 Using mysqldump to Test for Upgrade Incompatibilities</h4>

</div>

</div>

</div>
<p>
          When contemplating a MySQL upgrade, it is prudent to install
          the newer version separately from your current production
          version. Then you can dump the database and database object
          definitions from the production server and load them into the
          new server to verify that they are handled properly. (This is
          also useful for testing downgrades.)
        </p><p>
          On the production server:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --all-databases --no-data --routines --events &gt; dump-defs.sql</code></strong>
</pre><p>
          On the upgraded server:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql &lt; dump-defs.sql</code></strong>
</pre><p>
          Because the dump file does not contain table data, it can be
          processed quickly. This enables you to spot potential
          incompatibilities without waiting for lengthy data-loading
          operations. Look for warnings or errors while the dump file is
          being processed.
        </p><p>
          After you have verified that the definitions are handled
          properly, dump the data and try to load it into the upgraded
          server.
        </p><p>
          On the production server:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqldump --all-databases --no-create-info &gt; dump-data.sql</code></strong>
</pre><p>
          On the upgraded server:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql &lt; dump-data.sql</code></strong>
</pre><p>
          Now check the table contents and run some test queries.
</p>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="point-in-time-recovery"></a>7.5 Point-in-Time (Incremental) Recovery Using the Binary Log</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="backup-and-recovery.html#point-in-time-recovery-times">7.5.1 Point-in-Time Recovery Using Event Times</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#point-in-time-recovery-positions">7.5.2 Point-in-Time Recovery Using Event Positions</a></span></dt></dl>
</div>
<a class="indexterm" name="idm140710722245952"></a><a class="indexterm" name="idm140710722244912"></a><a class="indexterm" name="idm140710722243424"></a><a class="indexterm" name="idm140710722242352"></a><p>
      Point-in-time recovery refers to recovery of data changes made
      since a given point in time. Typically, this type of recovery is
      performed after restoring a full backup that brings the server to
      its state as of the time the backup was made. (The full backup can
      be made in several ways, such as those listed in
      <a class="xref" href="backup-and-recovery.html#backup-methods" title="7.2 Database Backup Methods">Section 7.2, “Database Backup Methods”</a>.) Point-in-time recovery then
      brings the server up to date incrementally from the time of the
      full backup to a more recent time.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
        Many of the examples here use the <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a>
        client to process binary log output produced by
        <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a>. If your binary log contains
        <code class="literal">\0</code> (null) characters, that output cannot be
        parsed by <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> unless you invoke it with the
        <a class="link" href="programs.html#option_mysql_binary-mode"><code class="option">--binary-mode</code></a> option.
</p>
</div>
<p>
      Point-in-time recovery is based on these principles:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          The source of information for point-in-time recovery is the
          set of incremental backups represented by the binary log files
          generated subsequent to the full backup operation. Therefore,
          the server must be started with the
          <a class="link" href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option to enable
          binary logging (see <a class="xref" href="server-administration.html#binary-log" title="5.4.4 The Binary Log">Section 5.4.4, “The Binary Log”</a>).
        </p><p>
          To restore data from the binary log, you must know the name
          and location of the current binary log files. By default, the
          server creates binary log files in the data directory, but a
          path name can be specified with the
          <a class="link" href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option to place the
          files in a different location. <a class="xref" href="server-administration.html#binary-log" title="5.4.4 The Binary Log">Section 5.4.4, “The Binary Log”</a>.
        </p><p>
          To see a listing of all binary log files, use this statement:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW BINARY LOGS;</code></strong>
</pre><p>
          To determine the name of the current binary log file, issue
          the following statement:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW MASTER STATUS;</code></strong>
</pre></li><li class="listitem"><p>
          The <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a> utility converts the events
          in the binary log files from binary format to text so that
          they can be executed or viewed. <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a>
          has options for selecting sections of the binary log based on
          event times or position of events within the log. See
          <a class="xref" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files">Section 4.6.7, “<span class="command"><strong>mysqlbinlog</strong></span> — Utility for Processing Binary Log Files”</a>.
        </p></li><li class="listitem"><p>
          Executing events from the binary log causes the data
          modifications they represent to be redone. This enables
          recovery of data changes for a given span of time. To execute
          events from the binary log, process
          <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a> output using the
          <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> client:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog <em class="replaceable"><code>binlog_files</code></em> | mysql -u root -p</code></strong>
</pre></li><li class="listitem"><p>
          Viewing log contents can be useful when you need to determine
          event times or positions to select partial log contents prior
          to executing events. To view events from the log, send
          <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a> output into a paging program:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog <em class="replaceable"><code>binlog_files</code></em> | more</code></strong>
</pre><p>
          Alternatively, save the output in a file and view the file in
          a text editor:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog <em class="replaceable"><code>binlog_files</code></em> &gt; tmpfile</code></strong>
shell&gt; ... <em class="replaceable"><code>edit tmpfile</code></em> ...
</pre></li><li class="listitem"><p>
          Saving the output in a file is useful as a preliminary to
          executing the log contents with certain events removed, such
          as an accidental <a class="link" href="sql-syntax.html#drop-database" title="13.1.22 DROP DATABASE Syntax"><code class="literal">DROP DATABASE</code></a>.
          You can delete from the file any statements not to be executed
          before executing its contents. After editing the file, execute
          the contents as follows:
        </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql -u root -p &lt; tmpfile</code></strong>
</pre></li></ul>
</div>
<p>
      If you have more than one binary log to execute on the MySQL
      server, the safe method is to process them all using a single
      connection to the server. Here is an example that demonstrates
      what may be <span class="emphasis"><em>unsafe</em></span>:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!</code></strong>
shell&gt; <strong class="userinput"><code>mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!</code></strong>
</pre><p>
      Processing binary logs this way using different connections to the
      server causes problems if the first log file contains a
      <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TEMPORARY
      TABLE</code></a> statement and the second log contains a statement
      that uses the temporary table. When the first
      <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> process terminates, the server drops the
      temporary table. When the second <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> process
      attempts to use the table, the server reports <span class="quote">“<span class="quote">unknown
      table.</span>”</span>
    </p><p>
      To avoid problems like this, use a <span class="emphasis"><em>single</em></span>
      connection to execute the contents of all binary logs that you
      want to process. Here is one way to do so:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p</code></strong>
</pre><p>
      Another approach is to write all the logs to a single file and
      then process the file:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog binlog.000001 &gt;  /tmp/statements.sql</code></strong>
shell&gt; <strong class="userinput"><code>mysqlbinlog binlog.000002 &gt;&gt; /tmp/statements.sql</code></strong>
shell&gt; <strong class="userinput"><code>mysql -u root -p -e "source /tmp/statements.sql"</code></strong>
</pre><p>
      When writing to a dump file while reading back from a binary log
      containing GTIDs (see <a class="xref" href="replication.html#replication-gtids" title="16.1.3 Replication with Global Transaction Identifiers">Section 16.1.3, “Replication with Global Transaction Identifiers”</a>), use
      the <a class="link" href="programs.html#option_mysqlbinlog_skip-gtids"><code class="option">--skip-gtids</code></a> option with
      <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a>, like this:
    </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog --skip-gtids binlog.000001 &gt;  /tmp/dump.sql</code></strong>
shell&gt; <strong class="userinput"><code>mysqlbinlog --skip-gtids binlog.000002 &gt;&gt; /tmp/dump.sql</code></strong>
shell&gt; <strong class="userinput"><code>mysql -u root -p -e "source /tmp/dump.sql"</code></strong>
</pre>
<div class="section">

<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="point-in-time-recovery-times"></a>7.5.1 Point-in-Time Recovery Using Event Times</h3>
</div>
</div>
</div>
<p>
        To indicate the start and end times for recovery, specify the
        <a class="link" href="programs.html#option_mysqlbinlog_start-datetime"><code class="option">--start-datetime</code></a> and
        <a class="link" href="programs.html#option_mysqlbinlog_stop-datetime"><code class="option">--stop-datetime</code></a> options for
        <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a>, in
        <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">DATETIME</code></a> format. As an example,
        suppose that exactly at 10:00 a.m. on April 20, 2005 an SQL
        statement was executed that deleted a large table. To restore
        the table and data, you could restore the previous night's
        backup, and then execute the following command:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog --stop-datetime="2005-04-20 9:59:59" \</code></strong>
         <strong class="userinput"><code>/var/log/mysql/bin.123456 | mysql -u root -p</code></strong>
</pre><p>
        This command recovers all of the data up until the date and time
        given by the <a class="link" href="programs.html#option_mysqlbinlog_stop-datetime"><code class="option">--stop-datetime</code></a>
        option. If you did not detect the erroneous SQL statement that
        was entered until hours later, you will probably also want to
        recover the activity that occurred afterward. Based on this, you
        could run <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a> again with a start date
        and time, like so:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog --start-datetime="2005-04-20 10:01:00" \</code></strong>
         <strong class="userinput"><code>/var/log/mysql/bin.123456 | mysql -u root -p</code></strong>
</pre><p>
        In this command, the SQL statements logged from 10:01 a.m. on
        will be re-executed. The combination of restoring of the
        previous night's dump file and the two
        <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a> commands restores everything up
        until one second before 10:00 a.m. and everything from 10:01
        a.m. on.
      </p><p>
        To use this method of point-in-time recovery, you should examine
        the log to be sure of the exact times to specify for the
        commands. To display the log file contents without executing
        them, use this command:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog /var/log/mysql/bin.123456 &gt; /tmp/mysql_restore.sql</code></strong>
</pre><p>
        Then open the <code class="filename">/tmp/mysql_restore.sql</code> file
        with a text editor to examine it.
      </p><p>
        Excluding specific changes by specifying times for
        <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a> does not work well if multiple
        statements executed at the same time as the one to be excluded.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="point-in-time-recovery-positions"></a>7.5.2 Point-in-Time Recovery Using Event Positions</h3>

</div>

</div>

</div>
<p>
        Instead of specifying dates and times, the
        <a class="link" href="programs.html#option_mysqlbinlog_start-position"><code class="option">--start-position</code></a> and
        <a class="link" href="programs.html#option_mysqlbinlog_stop-position"><code class="option">--stop-position</code></a> options for
        <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a> can be used for specifying log
        positions. They work the same as the start and stop date
        options, except that you specify log position numbers rather
        than dates. Using positions may enable you to be more precise
        about which part of the log to recover, especially if many
        transactions occurred around the same time as a damaging SQL
        statement. To determine the position numbers, run
        <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a> for a range of times near the
        time when the unwanted transaction was executed, but redirect
        the results to a text file for examination. This can be done
        like so:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog --start-datetime="2005-04-20 9:55:00" \</code></strong>
         <strong class="userinput"><code>--stop-datetime="2005-04-20 10:05:00" \</code></strong>
         <strong class="userinput"><code>/var/log/mysql/bin.123456 &gt; /tmp/mysql_restore.sql</code></strong>
</pre><p>
        This command creates a small text file in the
        <code class="filename">/tmp</code> directory that contains the SQL
        statements around the time that the deleterious SQL statement
        was executed. Open this file with a text editor and look for the
        statement that you do not want to repeat. Determine the
        positions in the binary log for stopping and resuming the
        recovery and make note of them. Positions are labeled as
        <code class="literal">log_pos</code> followed by a number. After restoring
        the previous backup file, use the position numbers to process
        the binary log file. For example, you would use commands
        something like these:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 \</code></strong>
         <strong class="userinput"><code>| mysql -u root -p</code></strong>

shell&gt; <strong class="userinput"><code>mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \</code></strong>
         <strong class="userinput"><code>| mysql -u root -p</code></strong>
</pre><p>
        The first command recovers all the transactions up until the
        stop position given. The second command recovers all
        transactions from the starting position given until the end of
        the binary log. Because the output of
        <a class="link" href="programs.html#mysqlbinlog" title="4.6.7 mysqlbinlog — Utility for Processing Binary Log Files"><span class="command"><strong>mysqlbinlog</strong></span></a> includes <code class="literal">SET
        TIMESTAMP</code> statements before each SQL statement
        recorded, the recovered data and related MySQL logs will reflect
        the original times at which the transactions were executed.
</p>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="myisam-table-maintenance"></a>7.6 MyISAM Table Maintenance and Crash Recovery</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="backup-and-recovery.html#myisam-crash-recovery">7.6.1 Using myisamchk for Crash Recovery</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#myisam-check">7.6.2 How to Check MyISAM Tables for Errors</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#myisam-repair">7.6.3 How to Repair MyISAM Tables</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#myisam-optimization">7.6.4 MyISAM Table Optimization</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#myisam-maintenance-schedule">7.6.5 Setting Up a MyISAM Table Maintenance Schedule</a></span></dt></dl>
</div>
<p>
      This section discusses how to use <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> to
      check or repair <code class="literal">MyISAM</code> tables (tables that have
      <code class="filename">.MYD</code> and <code class="filename">.MYI</code> files for
      storing data and indexes). For general
      <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> background, see
      <a class="xref" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility">Section 4.6.3, “<span class="command"><strong>myisamchk</strong></span> — MyISAM Table-Maintenance Utility”</a>. Other table-repair information can be
      found at <a class="xref" href="installing.html#rebuilding-tables" title="2.11.3 Rebuilding or Repairing Tables or Indexes">Section 2.11.3, “Rebuilding or Repairing Tables or Indexes”</a>.
    </p><p>
      You can use <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> to check, repair, or
      optimize database tables. The following sections describe how to
      perform these operations and how to set up a table maintenance
      schedule. For information about using <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>
      to get information about your tables, see
      <a class="xref" href="programs.html#myisamchk-table-info" title="4.6.3.5 Obtaining Table Information with myisamchk">Section 4.6.3.5, “Obtaining Table Information with myisamchk”</a>.
    </p><p>
      Even though table repair with <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> is
      quite secure, it is always a good idea to make a backup
      <span class="emphasis"><em>before</em></span> doing a repair or any maintenance
      operation that could make a lot of changes to a table.
    </p><p>
      <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> operations that affect indexes can
      cause <code class="literal">MyISAM</code> <code class="literal">FULLTEXT</code>
      indexes to be rebuilt with full-text parameters that are
      incompatible with the values used by the MySQL server. To avoid
      this problem, follow the guidelines in
      <a class="xref" href="programs.html#myisamchk-general-options" title="4.6.3.1 myisamchk General Options">Section 4.6.3.1, “myisamchk General Options”</a>.
    </p><p>
      <code class="literal">MyISAM</code> table maintenance can also be done using
      the SQL statements that perform operations similar to what
      <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> can do:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          To check <code class="literal">MyISAM</code> tables, use
          <a class="link" href="sql-syntax.html#check-table" title="13.7.2.2 CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a>.
        </p></li><li class="listitem"><p>
          To repair <code class="literal">MyISAM</code> tables, use
          <a class="link" href="sql-syntax.html#repair-table" title="13.7.2.5 REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a>.
        </p></li><li class="listitem"><p>
          To optimize <code class="literal">MyISAM</code> tables, use
          <a class="link" href="sql-syntax.html#optimize-table" title="13.7.2.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a>.
        </p></li><li class="listitem"><p>
          To analyze <code class="literal">MyISAM</code> tables, use
          <a class="link" href="sql-syntax.html#analyze-table" title="13.7.2.1 ANALYZE TABLE Syntax"><code class="literal">ANALYZE TABLE</code></a>.
</p></li></ul>
</div>
<p>
      For additional information about these statements, see
      <a class="xref" href="sql-syntax.html#table-maintenance-sql" title="13.7.2 Table Maintenance Statements">Section 13.7.2, “Table Maintenance Statements”</a>.
    </p><p>
      These statements can be used directly or by means of the
      <a class="link" href="programs.html#mysqlcheck" title="4.5.3 mysqlcheck — A Table Maintenance Program"><span class="command"><strong>mysqlcheck</strong></span></a> client program. One advantage of
      these statements over <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> is that the
      server does all the work. With <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>, you
      must make sure that the server does not use the tables at the same
      time so that there is no unwanted interaction between
      <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> and the server.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="myisam-crash-recovery"></a>7.6.1 Using myisamchk for Crash Recovery</h3>
</div>
</div>
</div>
<a class="indexterm" name="idm140710722100016"></a><a class="indexterm" name="idm140710722098560"></a><a class="indexterm" name="idm140710722097072"></a><a class="indexterm" name="idm140710722096000"></a><p>
        This section describes how to check for and deal with data
        corruption in MySQL databases. If your tables become corrupted
        frequently, you should try to find the reason why. See
        <a class="xref" href="error-handling.html#crashing" title="B.5.3.3 What to Do If MySQL Keeps Crashing">Section B.5.3.3, “What to Do If MySQL Keeps Crashing”</a>.
      </p><p>
        For an explanation of how <code class="literal">MyISAM</code> tables can
        become corrupted, see <a class="xref" href="storage-engines.html#myisam-table-problems" title="15.2.4 MyISAM Table Problems">Section 15.2.4, “MyISAM Table Problems”</a>.
      </p><p>
        If you run <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> with external locking
        disabled (which is the default), you cannot reliably use
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> to check a table when
        <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> is using the same table. If you can be
        certain that no one will access the tables through
        <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> while you run
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>, you only have to execute
        <a class="link" href="programs.html#mysqladmin" title="4.5.2 mysqladmin — Client for Administering a MySQL Server"><span class="command"><strong>mysqladmin flush-tables</strong></span></a> before you start
        checking the tables. If you cannot guarantee this, you must stop
        <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> while you check the tables. If you run
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> to check tables that
        <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> is updating at the same time, you may
        get a warning that a table is corrupt even when it is not.
      </p><p>
        If the server is run with external locking enabled, you can use
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> to check tables at any time. In
        this case, if the server tries to update a table that
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> is using, the server will wait for
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> to finish before it continues.
      </p><p>
        If you use <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> to repair or optimize
        tables, you <span class="emphasis"><em>must</em></span> always ensure that the
        <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> server is not using the table (this
        also applies if external locking is disabled). If you do not
        stop <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a>, you should at least do a
        <a class="link" href="programs.html#mysqladmin" title="4.5.2 mysqladmin — Client for Administering a MySQL Server"><span class="command"><strong>mysqladmin flush-tables</strong></span></a> before you run
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>. Your tables <span class="emphasis"><em>may become
        corrupted</em></span> if the server and
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> access the tables simultaneously.
      </p><p>
        When performing crash recovery, it is important to understand
        that each <code class="literal">MyISAM</code> table
        <em class="replaceable"><code>tbl_name</code></em> in a database corresponds to
        the three files in the database directory shown in the following
        table.
</p>
<div class="informaltable">
<table summary="The three files in the database directory that correspond to each MyISAM table."><col width="20%"><col width="40%"><thead><tr>
            <th scope="col">File</th>
            <th scope="col">Purpose</th>
          </tr></thead><tbody><tr>
            <td scope="row"><code class="filename"><em class="replaceable"><code>tbl_name</code></em>.frm</code></td>
            <td>Definition (format) file</td>
          </tr><tr>
            <td scope="row"><code class="filename"><em class="replaceable"><code>tbl_name</code></em>.MYD</code></td>
            <td>Data file</td>
          </tr><tr>
            <td scope="row"><code class="filename"><em class="replaceable"><code>tbl_name</code></em>.MYI</code></td>
            <td>Index file</td>
</tr></tbody></table>
</div>
<p>
        Each of these three file types is subject to corruption in
        various ways, but problems occur most often in data files and
        index files.
      </p><p>
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> works by creating a copy of the
        <code class="filename">.MYD</code> data file row by row. It ends the
        repair stage by removing the old <code class="filename">.MYD</code> file
        and renaming the new file to the original file name. If you use
        <a class="link" href="programs.html#option_myisamchk_quick"><code class="option">--quick</code></a>,
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> does not create a temporary
        <code class="filename">.MYD</code> file, but instead assumes that the
        <code class="filename">.MYD</code> file is correct and generates only a
        new index file without touching the <code class="filename">.MYD</code>
        file. This is safe, because <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>
        automatically detects whether the <code class="filename">.MYD</code> file
        is corrupt and aborts the repair if it is. You can also specify
        the <a class="link" href="programs.html#option_myisamchk_quick"><code class="option">--quick</code></a> option twice to
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>. In this case,
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> does not abort on some errors (such
        as duplicate-key errors) but instead tries to resolve them by
        modifying the <code class="filename">.MYD</code> file. Normally the use
        of two <a class="link" href="programs.html#option_myisamchk_quick"><code class="option">--quick</code></a> options is
        useful only if you have too little free disk space to perform a
        normal repair. In this case, you should at least make a backup
        of the table before running <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="myisam-check"></a>7.6.2 How to Check MyISAM Tables for Errors</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140710722030976"></a><a class="indexterm" name="idm140710722029520"></a><a class="indexterm" name="idm140710722028032"></a><p>
        To check a <code class="literal">MyISAM</code> table, use the following
        commands:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk
            <em class="replaceable"><code>tbl_name</code></em></strong></span></a>
          </p><p>
            This finds 99.99% of all errors. What it cannot find is
            corruption that involves <span class="emphasis"><em>only</em></span> the data
            file (which is very unusual). If you want to check a table,
            you should normally run <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> without
            options or with the <code class="option">-s</code> (silent) option.
          </p></li><li class="listitem"><p>
            <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -m
            <em class="replaceable"><code>tbl_name</code></em></strong></span></a>
          </p><p>
            This finds 99.999% of all errors. It first checks all index
            entries for errors and then reads through all rows. It
            calculates a checksum for all key values in the rows and
            verifies that the checksum matches the checksum for the keys
            in the index tree.
          </p></li><li class="listitem"><p>
            <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -e
            <em class="replaceable"><code>tbl_name</code></em></strong></span></a>
          </p><p>
            This does a complete and thorough check of all data
            (<code class="option">-e</code> means <span class="quote">“<span class="quote">extended check</span>”</span>).
            It does a check-read of every key for each row to verify
            that they indeed point to the correct row. This may take a
            long time for a large table that has many indexes. Normally,
            <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> stops after the first error it
            finds. If you want to obtain more information, you can add
            the <code class="option">-v</code> (verbose) option. This causes
            <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> to keep going, up through a
            maximum of 20 errors.
          </p></li><li class="listitem"><p>
            <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -e -i
            <em class="replaceable"><code>tbl_name</code></em></strong></span></a>
          </p><p>
            This is like the previous command, but the
            <code class="option">-i</code> option tells
            <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> to print additional statistical
            information.
</p></li></ul>
</div>
<p>
        In most cases, a simple <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> command
        with no arguments other than the table name is sufficient to
        check a table.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="myisam-repair"></a>7.6.3 How to Repair MyISAM Tables</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140710722002624"></a><a class="indexterm" name="idm140710722001168"></a><p>
        The discussion in this section describes how to use
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> on <code class="literal">MyISAM</code> tables
        (extensions <code class="filename">.MYI</code> and
        <code class="filename">.MYD</code>).
      </p><p>
        You can also use the <a class="link" href="sql-syntax.html#check-table" title="13.7.2.2 CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a>
        and <a class="link" href="sql-syntax.html#repair-table" title="13.7.2.5 REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a> statements to
        check and repair <code class="literal">MyISAM</code> tables. See
        <a class="xref" href="sql-syntax.html#check-table" title="13.7.2.2 CHECK TABLE Syntax">Section 13.7.2.2, “CHECK TABLE Syntax”</a>, and
        <a class="xref" href="sql-syntax.html#repair-table" title="13.7.2.5 REPAIR TABLE Syntax">Section 13.7.2.5, “REPAIR TABLE Syntax”</a>.
      </p><p>
        Symptoms of corrupted tables include queries that abort
        unexpectedly and observable errors such as these:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <code class="filename"><em class="replaceable"><code>tbl_name</code></em>.frm</code>
            is locked against change
          </p></li><li class="listitem"><p>
            Can't find file
            <code class="filename"><em class="replaceable"><code>tbl_name</code></em>.MYI</code>
            (Errcode: <em class="replaceable"><code>nnn</code></em>)
          </p></li><li class="listitem"><p>
            Unexpected end of file
          </p></li><li class="listitem"><p>
            Record file is crashed
          </p></li><li class="listitem"><p>
            Got error <em class="replaceable"><code>nnn</code></em> from table handler
</p></li></ul>
</div>
<p>
        To get more information about the error, run
        <a class="link" href="programs.html#perror" title="4.8.2 perror — Explain Error Codes"><span class="command"><strong>perror</strong></span></a> <em class="replaceable"><code>nnn</code></em>, where
        <em class="replaceable"><code>nnn</code></em> is the error number. The
        following example shows how to use <a class="link" href="programs.html#perror" title="4.8.2 perror — Explain Error Codes"><span class="command"><strong>perror</strong></span></a> to
        find the meanings for the most common error numbers that
        indicate a problem with a table:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>perror 126 127 132 134 135 136 141 144 145</code></strong>
MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired
</pre><p>
        Note that error 135 (no more room in record file) and error 136
        (no more room in index file) are not errors that can be fixed by
        a simple repair. In this case, you must use
        <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> to increase the
        <code class="literal">MAX_ROWS</code> and
        <code class="literal">AVG_ROW_LENGTH</code> table option values:
      </p><pre data-lang="sql" class="programlisting">
ALTER TABLE <em class="replaceable"><code>tbl_name</code></em> MAX_ROWS=<em class="replaceable"><code>xxx</code></em> AVG_ROW_LENGTH=<em class="replaceable"><code>yyy</code></em>;
</pre><p>
        If you do not know the current table option values, use
        <a class="link" href="sql-syntax.html#show-create-table" title="13.7.5.10 SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE TABLE</code></a>.
      </p><p>
        For the other errors, you must repair your tables.
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> can usually detect and fix most
        problems that occur.
      </p><p>
        The repair process involves up to four stages, described here.
        Before you begin, you should change location to the database
        directory and check the permissions of the table files. On Unix,
        make sure that they are readable by the user that
        <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> runs as (and to you, because you need
        to access the files you are checking). If it turns out you need
        to modify files, they must also be writable by you.
      </p><p>
        This section is for the cases where a table check fails (such as
        those described in <a class="xref" href="backup-and-recovery.html#myisam-check" title="7.6.2 How to Check MyISAM Tables for Errors">Section 7.6.2, “How to Check MyISAM Tables for Errors”</a>), or you want
        to use the extended features that <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>
        provides.
      </p><p>
        The <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> options used for table
        maintenance with are described in <a class="xref" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility">Section 4.6.3, “<span class="command"><strong>myisamchk</strong></span> — MyISAM Table-Maintenance Utility”</a>.
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> also has variables that you can set
        to control memory allocation that may improve performance. See
        <a class="xref" href="programs.html#myisamchk-memory" title="4.6.3.6 myisamchk Memory Usage">Section 4.6.3.6, “myisamchk Memory Usage”</a>.
      </p><p>
        If you are going to repair a table from the command line, you
        must first stop the <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> server. Note that
        when you do <a class="link" href="programs.html#mysqladmin" title="4.5.2 mysqladmin — Client for Administering a MySQL Server"><span class="command"><strong>mysqladmin shutdown</strong></span></a> on a remote
        server, the <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> server is still available
        for a while after <a class="link" href="programs.html#mysqladmin" title="4.5.2 mysqladmin — Client for Administering a MySQL Server"><span class="command"><strong>mysqladmin</strong></span></a> returns, until
        all statement-processing has stopped and all index changes have
        been flushed to disk.
      </p><p>
        <span class="bold"><strong>Stage 1: Checking your tables</strong></span>
      </p><p>
        Run <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk *.MYI</strong></span></a> or <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -e
        *.MYI</strong></span></a> if you have more time. Use the
        <code class="option">-s</code> (silent) option to suppress unnecessary
        information.
      </p><p>
        If the <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> server is stopped, you should
        use the <a class="link" href="programs.html#option_myisamchk_update-state"><code class="option">--update-state</code></a> option
        to tell <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> to mark the table as
        <span class="quote">“<span class="quote">checked.</span>”</span>
      </p><p>
        You have to repair only those tables for which
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> announces an error. For such
        tables, proceed to Stage 2.
      </p><p>
        If you get unexpected errors when checking (such as <code class="literal">out
        of memory</code> errors), or if <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>
        crashes, go to Stage 3.
      </p><p>
        <span class="bold"><strong>Stage 2: Easy safe repair</strong></span>
      </p><p>
        First, try <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -r -q
        <em class="replaceable"><code>tbl_name</code></em></strong></span></a> (<code class="option">-r
        -q</code> means <span class="quote">“<span class="quote">quick recovery mode</span>”</span>). This
        attempts to repair the index file without touching the data
        file. If the data file contains everything that it should and
        the delete links point at the correct locations within the data
        file, this should work, and the table is fixed. Start repairing
        the next table. Otherwise, use the following procedure:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
            Make a backup of the data file before continuing.
          </p></li><li class="listitem"><p>
            Use <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -r
            <em class="replaceable"><code>tbl_name</code></em></strong></span></a>
            (<code class="option">-r</code> means <span class="quote">“<span class="quote">recovery mode</span>”</span>).
            This removes incorrect rows and deleted rows from the data
            file and reconstructs the index file.
          </p></li><li class="listitem"><p>
            If the preceding step fails, use <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk
            --safe-recover
            <em class="replaceable"><code>tbl_name</code></em></strong></span></a>. Safe recovery
            mode uses an old recovery method that handles a few cases
            that regular recovery mode does not (but is slower).
</p></li></ol>
</div>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<div class="admon-title">
Note
</div>
<p>
          If you want a repair operation to go much faster, you should
          set the values of the
          <a class="link" href="server-administration.html#sysvar_sort_buffer_size"><code class="literal">sort_buffer_size</code></a> and
          <a class="link" href="server-administration.html#sysvar_key_buffer_size"><code class="literal">key_buffer_size</code></a> variables
          each to about 25% of your available memory when running
          <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>.
</p>
</div>
<p>
        If you get unexpected errors when repairing (such as
        <code class="literal">out of memory</code> errors), or if
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> crashes, go to Stage 3.
      </p><p>
        <span class="bold"><strong>Stage 3: Difficult repair</strong></span>
      </p><p>
        You should reach this stage only if the first 16KB block in the
        index file is destroyed or contains incorrect information, or if
        the index file is missing. In this case, it is necessary to
        create a new index file. Do so as follows:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
            Move the data file to a safe place.
          </p></li><li class="listitem"><p>
            Use the table description file to create new (empty) data
            and index files:
          </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>mysql <em class="replaceable"><code>db_name</code></em></code></strong>
</pre><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET autocommit=1;</code></strong>
mysql&gt; <strong class="userinput"><code>TRUNCATE TABLE <em class="replaceable"><code>tbl_name</code></em>;</code></strong>
mysql&gt; <strong class="userinput"><code>quit</code></strong>
</pre></li><li class="listitem"><p>
            Copy the old data file back onto the newly created data
            file. (Do not just move the old file back onto the new file.
            You want to retain a copy in case something goes wrong.)
</p></li></ol>
</div>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
<div class="admon-title">
Important
</div>
<p>
          If you are using replication, you should stop it prior to
          performing the above procedure, since it involves file system
          operations, and these are not logged by MySQL.
</p>
</div>
<p>
        Go back to Stage 2. <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -r -q</strong></span></a> should
        work. (This should not be an endless loop.)
      </p><p>
        You can also use the <code class="literal">REPAIR TABLE
        <em class="replaceable"><code>tbl_name</code></em> USE_FRM</code> SQL
        statement, which performs the whole procedure automatically.
        There is also no possibility of unwanted interaction between a
        utility and the server, because the server does all the work
        when you use <a class="link" href="sql-syntax.html#repair-table" title="13.7.2.5 REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a>. See
        <a class="xref" href="sql-syntax.html#repair-table" title="13.7.2.5 REPAIR TABLE Syntax">Section 13.7.2.5, “REPAIR TABLE Syntax”</a>.
      </p><p>
        <span class="bold"><strong>Stage 4: Very difficult repair</strong></span>
      </p><p>
        You should reach this stage only if the
        <code class="filename">.frm</code> description file has also crashed.
        That should never happen, because the description file is not
        changed after the table is created:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
            Restore the description file from a backup and go back to
            Stage 3. You can also restore the index file and go back to
            Stage 2. In the latter case, you should start with
            <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -r</strong></span></a>.
          </p></li><li class="listitem"><p>
            If you do not have a backup but know exactly how the table
            was created, create a copy of the table in another database.
            Remove the new data file, and then move the
            <code class="filename">.frm</code> description and
            <code class="filename">.MYI</code> index files from the other
            database to your crashed database. This gives you new
            description and index files, but leaves the
            <code class="filename">.MYD</code> data file alone. Go back to Stage
            2 and attempt to reconstruct the index file.
</p></li></ol>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="myisam-optimization"></a>7.6.4 MyISAM Table Optimization</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140710721893616"></a><a class="indexterm" name="idm140710721892160"></a><p>
        To coalesce fragmented rows and eliminate wasted space that
        results from deleting or updating rows, run
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> in recovery mode:
      </p><pre data-lang="sql" class="programlisting">
shell&gt; <strong class="userinput"><code>myisamchk -r <em class="replaceable"><code>tbl_name</code></em></code></strong>
</pre><p>
        You can optimize a table in the same way by using the
        <a class="link" href="sql-syntax.html#optimize-table" title="13.7.2.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> SQL statement.
        <a class="link" href="sql-syntax.html#optimize-table" title="13.7.2.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> does a table
        repair and a key analysis, and also sorts the index tree so that
        key lookups are faster. There is also no possibility of unwanted
        interaction between a utility and the server, because the server
        does all the work when you use <a class="link" href="sql-syntax.html#optimize-table" title="13.7.2.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE
        TABLE</code></a>. See <a class="xref" href="sql-syntax.html#optimize-table" title="13.7.2.4 OPTIMIZE TABLE Syntax">Section 13.7.2.4, “OPTIMIZE TABLE Syntax”</a>.
      </p><p>
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> has a number of other options that
        you can use to improve the performance of a table:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <a class="link" href="programs.html#option_myisamchk_analyze"><code class="option">--analyze</code></a> or
            <code class="option">-a</code>: Perform key distribution analysis. This
            improves join performance by enabling the join optimizer to
            better choose the order in which to join the tables and
            which indexes it should use.
          </p></li><li class="listitem"><p>
            <a class="link" href="programs.html#option_myisamchk_sort-index"><code class="option">--sort-index</code></a> or
            <code class="option">-S</code>: Sort the index blocks. This optimizes
            seeks and makes table scans that use indexes faster.
          </p></li><li class="listitem"><p>
            <a class="link" href="programs.html#option_myisamchk_sort-records"><code class="option">--sort-records=<em class="replaceable"><code>index_num</code></em></code></a>
            or <code class="option">-R <em class="replaceable"><code>index_num</code></em></code>:
            Sort data rows according to a given index. This makes your
            data much more localized and may speed up range-based
            <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a> and <code class="literal">ORDER
            BY</code> operations that use this index.
</p></li></ul>
</div>
<p>
        For a full description of all available options, see
        <a class="xref" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility">Section 4.6.3, “<span class="command"><strong>myisamchk</strong></span> — MyISAM Table-Maintenance Utility”</a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="myisam-maintenance-schedule"></a>7.6.5 Setting Up a MyISAM Table Maintenance Schedule</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm140710721867808"></a><a class="indexterm" name="idm140710721866320"></a><p>
        It is a good idea to perform table checks on a regular basis
        rather than waiting for problems to occur. One way to check and
        repair <code class="literal">MyISAM</code> tables is with the
        <a class="link" href="sql-syntax.html#check-table" title="13.7.2.2 CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a> and
        <a class="link" href="sql-syntax.html#repair-table" title="13.7.2.5 REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a> statements. See
        <a class="xref" href="sql-syntax.html#table-maintenance-sql" title="13.7.2 Table Maintenance Statements">Section 13.7.2, “Table Maintenance Statements”</a>.
      </p><p>
        Another way to check tables is to use
        <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a>. For maintenance purposes, you can
        use <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -s</strong></span></a>. The <code class="option">-s</code>
        option (short for <a class="link" href="programs.html#option_myisamchk_silent"><code class="option">--silent</code></a>)
        causes <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk</strong></span></a> to run in silent mode,
        printing messages only when errors occur.
      </p><a class="indexterm" name="idm140710721854800"></a><p>
        It is also a good idea to enable automatic
        <code class="literal">MyISAM</code> table checking. For example, whenever
        the machine has done a restart in the middle of an update, you
        usually need to check each table that could have been affected
        before it is used further. (These are <span class="quote">“<span class="quote">expected crashed
        tables.</span>”</span>) To cause the server to check
        <code class="literal">MyISAM</code> tables automatically, start it with
        the <a class="link" href="server-administration.html#option_mysqld_myisam-recover-options"><code class="option">--myisam-recover-options</code></a>
        option. See <a class="xref" href="server-administration.html#server-options" title="5.1.6 Server Command Options">Section 5.1.6, “Server Command Options”</a>.
      </p><p>
        You should also check your tables regularly during normal system
        operation. For example, you can run a <span class="command"><strong>cron</strong></span>
        job to check important tables once a week, using a line like
        this in a <code class="filename">crontab</code> file:
      </p><pre data-lang="simple" class="programlisting">
35 0 * * 0 <em class="replaceable"><code>/path/to/myisamchk</code></em> --fast --silent <em class="replaceable"><code>/path/to/datadir</code></em>/*/*.MYI
</pre><p>
        This prints out information about crashed tables so that you can
        examine and repair them as necessary.
      </p><p>
        To start with, execute <a class="link" href="programs.html#myisamchk" title="4.6.3 myisamchk — MyISAM Table-Maintenance Utility"><span class="command"><strong>myisamchk -s</strong></span></a> each
        night on all tables that have been updated during the last 24
        hours. As you see that problems occur infrequently, you can back
        off the checking frequency to once a week or so.
      </p><a class="indexterm" name="idm140710721843136"></a><p>
        Normally, MySQL tables need little maintenance. If you are
        performing many updates to <code class="literal">MyISAM</code> tables with
        dynamic-sized rows (tables with
        <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a>,
        <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a>, or
        <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">TEXT</code></a> columns) or have tables with
        many deleted rows you may want to defragment/reclaim space from
        the tables from time to time. You can do this by using
        <a class="link" href="sql-syntax.html#optimize-table" title="13.7.2.4 OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> on the tables in
        question. Alternatively, if you can stop the
        <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> server for a while, change location
        into the data directory and use this command while the server is
        stopped:
      </p><pre data-lang="terminal" class="programlisting">
shell&gt; <strong class="userinput"><code>myisamchk -r -s --sort-index --myisam_sort_buffer_size=16M */*.MYI</code></strong>
</pre>
</div>

</div>

</div>
<div class="copyright-footer">

</div>
<div class="navfooter">
<hr>
<table width="100%" summary="Navigation footer">
<tr>
<td width="40%" align="left"><a accesskey="p" href="security.html">Prev</a></td>
<td width="20%" align="center"><a accesskey="u" href="">Up</a></td>
<td width="40%" align="right"> <a accesskey="n" href="optimization.html">Next</a></td>
</tr>
<tr>
<td width="40%" align="left" valign="top">Chapter 6 Security</td>
<td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td>
<td width="40%" align="right" valign="top">Chapter 8 Optimization</td>
</tr>
</table>
</div>
</body>
</html>
